Date Conditional Formatting

[Ray]

I want to be able to make the cell change a color when the date entered into
the cell has reached one of the three time lines: Green: Dates between date
in cell and 20 days after, Yellow: Date 21 days after date in cell, and Red:
Date 28 days after date in cell. I know how to change the color but the date
formulas are giving me trouble. Any help would be appreciated. Thanks.

 

[ryguy7272]

If your date is in A1, in B1 you could do: =(A1)+20 enter then right-click
the cell and format it as a date. Does that help? Repeat for the 21 days
and 28 days.

Conditional Formatting:
http://www.contextures.com/xlCondFormat01.html

 

[Bernard Liengme]

Suppose A1 has the test date (example 1-Dec-2009 but in any format you like)
And B1 has the date to be tested

Select B1 (and any other cells to have the conditional formatting)
Separately enter these three formulas
=B1-$A$1<=20 ... set fill or font colour to green (B1's date is up to 20
days later than A1's date)
=AND(B1-$A$1>20,B1-$A$1<=28)... yellow (B1's date is up to 28 days later
than A1's date)
=B1-$A$1>28 ... red


In all case you could replace A1 by an actual date. For example
=AND(B1-DATE(2001,12,1)>20,B1-DATE(2009,12,1)<=28)

I prefer to use DATE rather than DATEVALUE as it avoids the date format (US
vs rest) confusion

best wishes

 

[Lars Klintholm]

ON 19-12-2009 Ray wrote in microsoft.public.excel.worksheet.functions:
Try this
Conditional formating:
Condition1:
Cell value is greater than =TODAY()+28, Format Red
Condition 2:
Cell value is greater than =TODAY()+21, Format Yellow
Condition 3:
Cell value is greater than =TODAY(), Format Green

Regards
Lars Klintholm

 

[Ray]

They work great if I'm using two different cells but I want to enter the date
once and have the cell change color based on the computer calendar. I hope
that makes sense.