Highlight dates that are 3 years old as of the current date.

Hello. I have a column of various dates, and I want to set up a couple conditional formatting rules that will highlight a given cell yellow if the date is 3 years old as of the current date, or highlight the cell red if the date is 4 years old as of the current date. Even if it is possible to do this with one rule, I would probably just use 2 rules to keep it simple.

Thanks in advance for the help!

yes

you could use
cell to check < =DATE(YEAR(TODAY())-4,MONTH(TODAY()),DAY(TODAY()))
highlight red
set Stop if True
then
cell to check < =DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY()))
and highlight yellow

you will need two rules as the colour is changing

I assume you know how to bring up the CF dialogue box having selected "Use a formula ...". First Rule (check for oldest first):

=AND($D2<>"",TODAY()-$D2>=4*365)

then format as red (Note, I have assumed 365 days in a year). Second Rule:

=AND($D2<>"",TODAY()-$D2>=3*365)

then format as yellow.

These assume that you have selected all the data to be governed by the CF beforehand, and that the date of interest is in column D.

Hope this helps.

Pete

That worked great, thanks much.