Conditional formatting from Dates

[DavidM]

Today's date will be manually entered in to a header cell.
The worksheet contains the dates that customers last purchased from me.
How can I change the colour of the customer dates (and other details) eg. To
Red if older than a year, to Green if less than a year?
Apologies if this has a simple/obvious solution!
Many thks,

 

[Jacob Skaria]

Hi David

Excel will be able to identify the current date. So you dont need to enter
the current date to the header cell. Try the below in cell A1 of a fresh
workbook and feedback

From menu Format>Conditional Formatting>

In Condition1 select 'Formula Is' and enter
=DATEDIF(A1,TODAY(),"y")>0
and select red color from Format>Pattern

In Condition2 select 'Formula Is' and enter
=DATEDIF(A1,TODAY(),"y")=0
and select green color from Format>Pattern

If this post helps click Yes

 

[DavidM]

Thank you Jacob - that works absolutely great!

I wonder if you could explain it to me as I fail to see where "it"
understands the 12 months!

Thks

 

[David Biddulph]

1 year = 12 months.

If you wanted you could use
=DATEDIF(A1,TODAY(),"y")>=1 or =DATEDIF(A1,TODAY(),"m")>=12 or
=DATEDIF(A1,TODAY(),"m")>11
instead of
=DATEDIF(A1,TODAY(),"y")>0