Expiring Date formula?

[hsarrategui]

I am tracking our vendor's insurance and need the expiration dates to turn red when they are either about to expire or have expired. I can't seem to find anything to help me with this. If the date is 4/12/24 then the cell should be red until I get the new cert and change the date. and then the cell shouldn't turn red again until the new date has passed. Ideally I'd like the box to turn yellow when its about to expire so I can reach out and avoid going red all together but I am not great with computers and even less knowledgeable when it comes to excel. Column D has Liability date and column E has workers comp date. They are not always the same so I need to be able to see in color when the expiration date is approaching or once it has approached. Is this making sense? LOL

 

[MARK858]

Liability date and column E has workers comp

So which of the 2 is the expiration date? the liability?

 

[hsarrategui]

So which of the 2 is the expiration date? the liability?

both of them are expiration dates for different types of insurance. some have the same date but some differ.

 

[MARK858]

So what dates are we comparing to what dates? the ones shown to each other? Please explain in reference to what you are showing in the image, no other terms

 

[hsarrategui]

So what dates are we comparing to what dates? the ones shown to each other? Please explain in reference to what you are showing in the image, no other terms

I appreciate your patience with me. I want each cell with a date to turn red as the date in the cell is approaching.

 

[Alex Blakenburg]

See if this works for you.

Copy it into a new sheet with D1 as the activecell.
I would usually use Range Names for the Reporting Date and the Expiry Days Lead time.
Personal preference but I don't like having Today() hard coded in a formula so I have it in the Reporting Date box.

Book2
	D	E	F	G	H	I	J
1	Liablity	Worker's Comp				Reporting Date	11/10/2024
2	14/10/2024	8/10/2024				About to Expire Days	5
3
4
Sheet1

Cell Formulas
Range	Formula
J1	J1	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:E10	Expression	=D2>$J$1	text	YES
D2:E10	Expression	=D2>($J$1-$J$2)	text	NO