Gradient Color based on todays date

[BoredSean]

Hello, I am sorry I am having to ask such a simple question for experts on this board, but I have tried google and I am clueless. I have one column. In that column are dates that personnel qualified. It expires after one year. I just want to put the dates in and see the color change from something like green (completed qualification recently) to red (expired or close to) If this is impossible, what would be the next closest solution? Thank you in advance for the help.

 

[Cubist]

Something like this?

Book1
	A
1	Date qualified
2	1/1/23
3	1/1/24
4	6/23/24
5	12/8/22
Sheet6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=AND(TODAY()<=EDATE(A2,12),A2<>"")	text	NO
A2:A7	Expression	=AND(TODAY()>EDATE(A2,12),A2<>"")	text	YES

 

[BoredSean]

Yes, but is it possible to do it with gradient colors instead of 2 tone? If not I ca nmake due.

 

[Cubist]

What would the gradient colours be? There are potentially 365 days.

 

[BoredSean]

I know excel had a CF that includes gradient colors, changing from one color to the next, but its options for the formatting don't seem to fit this. For example it might start green, slowly start to move towards something brighter like yellow or orange and then eventually go deeper into red. I guess the gradient is something the computer does. Thank you for your help and contribution.

 

[Cubist]

Perhaps this. Negative numbers indicate a passed expiration date.

Book1
	A	B	C
1	Date qualified	Expiration Date	Days until Expiration Date
2	1/1/23	1/1/24	-156
3	1/1/24	1/1/25	210
4	6/23/24	6/23/25	383
5	12/8/22	12/8/23	-180
Sheet6

Cell Formulas
Range	Formula
B2:B5	B2	=EDATE(A2,12)
C2:C5	C2	=B2-TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C5	Other Type	Color scale		NO

 

[BoredSean]

Thats great! Thank you so much for your help!

 

[Cubist]

No problem. I would cap at 0 like this.

Book1
	A	B	C
1	Date qualified	Expiration Date	Days until Expiration Date
2	1/1/23	1/1/24	0
3	1/1/24	1/1/25	210
4	6/25/23	6/25/24	20
5	12/8/22	12/8/23	0
Sheet6

Cell Formulas
Range	Formula
B2:B5	B2	=EDATE(A2,12)
C2:C5	C2	=MAX(0,B2-TODAY())

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C5	Other Type	Color scale		NO