Conditional Format

S

Scottmk

Hi
I have a cell that =today(). Then, I have a column of due dates fo
rows. I would like to set up a conditional format that turns the du
date cells green, yellow, or red if they are 3,2,or 1 week(s) out. An
ideas?
Thank
 
F

filky

I've done something like this before where I used

=IF(A4="","",IF(D4<TODAY(),"Late","Open"))

to enter a descriptive word into one of the cells of my spreadsheet
then used

Cell Value is equal to ="Late" in the conditional formatting to tur
the cell red with white writing.

Might help to get some ideas
 
S

Scottmk

Thanks for the reply...I see what you are saying, but I would like to b
able to do this without adding another column. Otherwise, I would jus
make one that says "days remaining" and do a standard conditiona
format. Thank
 
F

filky

I see what you mean. I tried to use

=TODAY()-$G9>0
=TODAY()-$G9>7
=TODAY()-$G9>14

All these return TRUE or FALSE values in a cell but I can't get this t
be recognised in the conditional formatting boxes.

Hope someone can sort it
 
S

SidBord

Enclose your expression in parenthenses before entering the
comparison operator: =(Today()-$G9)>0
Actually, since you are comparing dates, you probably have
to use the DATEVALUE function for $G9:
=Today()-DATEVALUE(G9) > 0
 
D

Debra Dalgleish

Select the cells that you want to format
Choose Format>Conditional Formatting
Leave the first dropdown as Cell Value Is
From the next dropdown, choose Between
In the next box, type: =TODAY()
In the final box, type: =TODAY()+7
Click the Format button, and choose Red on the Patterns tab
Click OK

Click the Add button, and set the next condition to
Between =TODAY()+7 and =TODAY()+14
Format it as yellow

Click the Add button, and set the next condition to
Between =TODAY()+14 and =TODAY()+21
Format it as green
Click OK to close the dialog box
 
Top