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

[ThomasWade]

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.)

[#2 · Accepted solution]

@ThomasWade 

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.

[#3]

@ThomasWade 

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

Thank you for your time.

Nikolino

I know I don't know anything (Socrates)

[#4]

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

[#5]

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

[#6]

@karbley 

=A1 > TOADY() - 15

[#7]

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

[#8]

@karbley 

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

[#9]

@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

[#10]

@CaptYankee330 

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

it will be

[#11]

@Sergei Baklan that did it - thank you

[#12]

@CaptYankee330 , glad it helped

[#13]

@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?

[#14]

@DieselWSM 

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

=EDATE(TODAY(),-33)

etc.

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

=EDATE(TODAY(),-57)

etc.

[#15]

Thanks!