Conditional format 30-60-90 days to 1year old

New Contributor
I am trying to automatically highlight a date when it is 30-60-90 days from its due date but I want to use the date a certificate was issued.
Example: I have a cert that was issued on 5-26-2021 and expires 1 year from that date.

In the photo, I want that cell to highlight when today is less than 30-60-90 days from 26-may-2022.
I want to keep the issued date in the cell but I want it to highlight when it is 30-60-90 days from its 1 year expiry date(without having to add expiry date. I don't want more info than needed.)
14 Replies
best response confirmed by ThomasWade (New Contributor)


Select the cells that you want to format.


On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'less than or equal to' from the second drop-down.

In the box next to it, enter the formula =EDATE(TODAY(),-9)

Click Format...

Activate the Fill tab.

Select yellow.

Click OK, then click OK again.


Repeat the above steps, but with the formula =EDATE(TODAY(),-10) and orange as fill color.


Finally, repeat them again, this time with the formula =EDATE(TODAY(),-11) and red as fill color.


With permission of all, here are some additional examples for the solution proposed by Mr. Hans Vogelaar.


Thank you for your time.



I know I don't know anything (Socrates)

So simple and I still missed it originally. Thank you! I definitely take a step away from the excel work more often.


@Hans Vogelaar What formula would I use to make a date change color after 15 and 30 days? =EDATE(today(),-15) 


=A1 > TOADY() - 15
So, I am applying three rules to these cells?
1. cell is green
2. cell changes to yellow after 15 days
3. cell changes to red after 30 days


Yes. One colour - one rule. To the cell or to the range, depends on your data.

@Sergei Baklan Thank you!

I was able to use this answer to apply 3 rules to an entire row based on one cell in that row. 

But I can't figure out an easy wasy to propagate those 3 rules to all rows but keep it relative.

I'm not sure if I need to use a name or ???

If you can provide simple instructions that'd be good so I know how to do it myself next time.


I've attached my file for reference


You need to use relative reference for rows and apply the rule to entire range. Instead of


it will be


@Sergei Baklan that did it - thank you

@Hans Vogelaar This is an older thread but useful! Thanks. I have the same question but what is the formula when the expiration is 3 or 5 years from the date entered in the cell?


3 years is 36 months, so 3, 2 and 1 months before the expiration date is 33, 34 and 35 months from the start date. So use



5 years is 60 months, so 3, 2 and 1 months before the expiration date is 57, 58 and 59 months from the start date. So use