Conditional formatting with dates

[aivoryuk]

Hi I have had a search for this but can't seem to find anything that works for me.

I am using 2003 and need 3 conditional formats

If the date is greater than 21 days before todays date then it turns the cell red.
If the date is between 14 and 21 days before todays date then it goes yellow

If the date/cell is blank or less than 14 days before todays date it remains white.

I have tried a couple of ways but can't seem to get this to work.

Thanks

 

[jasonb75]

Try

First Rule (Red fill)

=TODAY()-A1>21

Second Rule (Yellow fill)

=TODAY()-A1>14

In both formula change A1 to the first cell in your range of dates.

 

[Gerald Higgins]

Just to be clear, by "greater than 21 days before todays date" I assume you mean, if the date is 21 days or more, before today's date. And not, if the date is later than the date 21 days before today's date.

For red...
Format
Conditional Formating
Cell Value Is
Less than
=today()-21
and format as red.

Add a second condition for
=today()-14
for yellow.

 

[aivoryuk]

hi all that worked.

further to this rather than just highlighting the cell I now need for it to highlight the whole row.
My date cells are in column O
what is the best way to ahieve this?

 

[jasonb75]

Make the column absolute in the formula

=TODAY()-$O1>21

and apply to the desired range.

 

[Gerald Higgins]

Use the FORMULA IS option instead of CELL VALUE IS.
Something like this

=$O1< (today()-21)