Conditional Formatting (CF) Dates

[Craigos]

Hi All,

I actually have 2 problems I cannot find answers to as such... 1 is with CF and Dates and the other counting the CF dates and despite much searching I cannot find the answers:

My CF cell fill issue:

I need to CF dates as follows:

1. Input a date...up to 14 days from that date the cell = Green
2. Between 15 and 28 days = Amber - this one gets me as I cannot work out the < and > formula
3. From day 29 = Red

Using Excel 2003

Many Thanks

Craigos

 

[Aladin Akyurek]

Select the range of interest, say, A2:A10...

Condition 1
Formula Is

=AND(ISNUMBER($A2), TODAY()-$A2 <= 14)

--> Green

Codition 2
Formula Is

=AND(ISNUMBER($A2), TODAY()-$A2 >= 15, TODAY() <= 28)

---> Amber

Condition 3
Formula Is

=AND(ISNUMBER($A2), TODAY()-$A2 > 28)

---> Red

 

[Craigos]

Aladin,

Thank you for such a quick response...I have put formula's in and no matter what the AMBER condition will not CF cell....Green =Brill, Red = Brill...Amber = Fail...does not apply any CF at all...any ideas?

Craigos

 

[Aladin Akyurek]

Aladin,

Thank you for such a quick response...I have put formula's in and no matter what the AMBER condition will not CF cell....Green =Brill, Red = Brill...Amber = Fail...does not apply any CF at all...any ideas?

Craigos

Try...

=AND(ISNUMBER($A2), TODAY()-$A2 >= 15, TODAY()-$A2 <= 28)

for the last term was missing -$A2...

 

[Craigos]

Aladin,

Perfect...many many thanks

Craigos

 

[Aladin Akyurek]

Aladin,

Perfect...many many thanks

Craigos

You are welcome. Thanks for providing feedback.