Conditional formatting for date

[WHOOSH]

Hi

I need to set up conditional formatting based on 3 years from a date and 5 years from a date.


e.g date set is 20/01/2010 need each cell in row to turn amber after 35 months then red when 36 months is reached.

 

[Faseeh]

Hi WHOOSH,


Welcome to the Chandoo's Forums!!!


Go to conditional formatting section and select New Rule > Use formula to format cells and paste following formula in provided space:

=$E$3>EDATE($D$3,35)

Where E3 is the date to be evaluated, and D3 is the starting date.


Regards,

Faseeh

 

[Luke M]

Expanding on Faseeh's comment


I believe the first formula should be

=E3>=EDATE($D$3,36)

and the 2nd formula would be:

=E3>=EDATE($D$3,35)


You don't want the dollar signs, as the formula needs to be relative for each cell within that CF range. The order of the formulas is important as, by default, XL will evaluate the CF formulas one at a time and then stop when it finds one that is True.

 

[bobhc]

Good day all

I must be reading the question wrong and would appreciate it if you could tell me where.


"3 years from a date and 5 years from a date" and "35 months then red when 36 months"

surly should the months not be 36 and 60 months

 

[SirJB7]

Hi, WOOSH!

I agree with b(ut)ob(ut)hc...

From a given date: a) 0 thru 35 months amber, 36 thru 60 months red; or b) 0-35 nothing, 36-59 amber, 60+ red?

Regards!