Conditional formatting from Dates
 

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,
--
DavidM

Conditional formatting from Dates
 

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 FormatConditional Formatting

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

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

If this post helps click Yes
---------------
Jacob Skaria


"DavidM" wrote:

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,
--
DavidM

Conditional formatting from Dates
 

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
--
DavidM


"Jacob Skaria" wrote:

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 FormatConditional Formatting

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

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

If this post helps click Yes
---------------
Jacob Skaria


"DavidM" wrote:

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,
--
DavidM

Conditional formatting from Dates
 

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
--
David Biddulph


"DavidM" wrote in message
...
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
--
DavidM


"Jacob Skaria" wrote:

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 FormatConditional Formatting

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

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

If this post helps click Yes
---------------
Jacob Skaria


"DavidM" wrote:

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,
--
DavidM