Conditional Formatting - Future expiration date

Question

I'm trying to set up a records checklist for work. I have an Excel sheet to keep the expiration dates of certain documents. Here's an example of what I am looking for:

Susan had her physical on 6/1/23. Physicals are valid for 2 years, so the expiration date is 6/1/25. In the cell I want to have the ACTUAL date of the physical (NOT the expiration date).
I'd like the cell with the date to turn yellow when it's going to expire within 45 days. Orange/Amber when it's going to expire within 20 days. Red when it has expired.

I also have some other documents that I want to have the actual date in the cell, but it's only valid for one year.

I hope this makes sense. Any help would be greatly appreciated! Thank you!

Answer 1

See the answer to the same question in https://learn.microsoft.com/en-us/answers/questions/1404447/excel-setting-up-conditional-formatting-for-skill.

Answer 2

Hi,

I created a sample:

Select Column A2:A7- go to Home tab- Conditional Formatting- New Rule...:

Select Use a formula to determine which cells to format- Enter the formula and Format...:

Expire within 45 days enter the formula and Format cell fill Orange: =AND(C2-TODAY()>20,C2-TODAY()<=45)

Expire within 20 days enter the formula and Format cell fill Orange: =C2-TODAY()<=20

Hope it's helpful.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.