Conditional Formatting with Color Gradient

[rscraig11]

I'm trying to create a color gradient conditional formatting for scheduling visit windows. So these visits have a target date with a minimum visit window 3 months before the target date and a maximum window date of 7 months after the target date. I'd like to make it so that when the minimum visit window date equals today then the target date turns green and then hits yellow when the target date equals today and goes to red when we hit the maximum visit window. So for example: The target date is 8/3/2024 with the visit window opening today, 5/3/2024, so the target date cell would be green as of today. As we near the target date, it would turn yellow and then as we near 3/2/2025 (the end of the window) it would turn red. Is this possible with Excel?

 

[myall_blues]

I don't know how your data are arranged, but something like this? I've shown the minimum and maximum dates for demonstration purposes, but in reality all you need is the target date. Wherever you see reference to A2 in the conditional formatting formulas, replace it with TODAY().

Book1
	A	B	C	D
1	"Today"	Min visit window	Target date	Max visit window
2	1/02/2024	4/02/2024	4/05/2024	4/12/2024
3	4/02/2024	4/02/2024	4/05/2024	4/12/2024
4	15/03/2024	4/02/2024	4/05/2024	4/12/2024
5	4/05/2024	4/02/2024	4/05/2024	4/12/2024
6	15/08/2024	4/02/2024	4/05/2024	4/12/2024
7	4/12/2024	4/02/2024	4/05/2024	4/12/2024
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=DATE(YEAR(C2),MONTH(C2)-3,DAY(C2))
D2:D7	D2	=DATE(YEAR(C2),MONTH(C2)+7,DAY(C2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=AND(A2>=DATE(YEAR(C2),MONTH(C2)-3,DAY(C2)),A2<C2)	text	NO
A2:A7	Expression	=AND(A2>=C2,A2<DATE(YEAR(C2),MONTH(C2)+7,DAY(C2)))	text	NO
A2:A7	Expression	=A2>=DATE(YEAR(C2),MONTH(C2)+7,DAY(C2))	text	NO

 

[rscraig11]

Thank you - is it possible to do this with a gradient on the target date?? so as the window opens it's green, fades to yellow as it nears the target date and then red as we reach the max date?

Data is set up like this:

Min Date	Target Date	Max Date
5/6/2024	8/6/2024	3/6/2025
2/1/2024	5/1/2024	12/1/2024
7/10/2023	10/10/2023	5/10/2024

Where just the target date would be changing and the top would be green (min date hit), the middle would be yellow, and the bottom would be red because we're nearing the end of the window. But I'd like it to be a gradient to it fades to red. Then after the max date it goes to black or something and before we hit the min date for the first column it isn't colored.

Thanks!

 

[myall_blues]

Apologies I missed the gradient part. What you're after is not exactly possible using colour gradients, but you can get close as long as you don't mind using 90 days and 210 days or thereabouts for 3 months and 7 months respectively.

Here is how to set up the colour gradient.

And you require two additional rules to remove the colour at each end of the range.

Book1
	A	B	C	D	E
1		Min Date	Target Date	Max Date
2		6/11/2023	6/02/2024	6/09/2024
3		7/11/2023	7/02/2024	7/09/2024
4		21/11/2023	21/02/2024	21/09/2024
5		6/12/2023	6/03/2024	6/10/2024
6		20/12/2023	20/03/2024	20/10/2024
7		3/01/2024	3/04/2024	3/11/2024
8		17/01/2024	17/04/2024	17/11/2024
9		1/02/2024	1/05/2024	1/12/2024
10	Today ===>	7/02/2024	7/05/2024	7/12/2024	<===
11		21/02/2024	21/05/2024	21/12/2024
12		4/03/2024	4/06/2024	4/01/2025
13		18/03/2024	18/06/2024	18/01/2025
14		2/04/2024	2/07/2024	2/02/2025
15		16/04/2024	16/07/2024	16/02/2025
16		30/04/2024	30/07/2024	2/03/2025
17		13/05/2024	13/08/2024	13/03/2025
18		27/05/2024	27/08/2024	27/03/2025
19		10/06/2024	10/09/2024	10/04/2025
20		24/06/2024	24/09/2024	24/04/2025
21		8/07/2024	8/10/2024	8/05/2025
22		22/07/2024	22/10/2024	22/05/2025
23		5/08/2024	5/11/2024	5/06/2025
24		19/08/2024	19/11/2024	19/06/2025
25		3/09/2024	3/12/2024	3/07/2025
26		4/09/2024	4/12/2024	4/07/2025
27
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C26	Expression	=$C2<TODAY()-90	text	YES
C2:C26	Expression	=$C2>TODAY()+210	text	YES
C2:C26	Other Type	Color scale		NO