Conditional formatting from Dates

  • Thread starter Thread starter DavidM
  • Start date Start date
D

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,
 
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
 
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
 
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
 
Back
Top