Automatic updating of Conditional Formats using dates

[Guest]

I've set up a spreadsheet using conditional formats and dates for the first
time.

The conditions are as follows If Cell Value is greater than =TODAY(0)+60 -
condition green

If Cell Value is between =TODAY(0)+60 and TODAY(0)+20 - condition orange

If Cell Value is less =TODAY(0)+20 - condition red

The conditions work but DO NOT AUTOMATICALLY RE-CALCULATE and so DO NOT FLAG
CRITICAL DATES. Eg if a date when I entered it more than 60 days from the
current date it would of course be green. Two weeks later when I open the
spreadsheet and rthe date shown is now only 50 days from TODAY it still shows
green and not orange.

If I change the date and then change it back it will show orange but will
not automatically update the condition.

Is there anyway I can do this?

 

[David McRitchie]

Try using =TODAY() instead of =TODAY(0)
if you type them into a worksheet the first will work the other will fail.

If Cell Value is between =TODAY(0)+60 and TODAY(0)+20 - condition orange

I think you want to use formulas, if A1 is the active cell

CF1: =AND(A1<=(TODAY()+60,A1>=TODAY()+20)
CF2: =A1<=(TODAY()+20)

at least for a range for between put the lower number first.
For more information on Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If you are using Conditional Formatting, the change will be immediate.
You had a problem with your formulas (use of TODAY().

 

[Peo Sjoblom]

set calculations to automatic under tools>options>calculations

 

[Peo Sjoblom]

I don't think you can enter today like that, both the conditional formatting
and regular cell entries refuse to accept it so I don't understand how the
OP can get any results at all?

--
Regards,

Peo Sjoblom

Try using =TODAY() instead of =TODAY(0)
if you type them into a worksheet the first will work the other will fail.

If Cell Value is between =TODAY(0)+60 and TODAY(0)+20 - condition orange

I think you want to use formulas, if A1 is the active cell

CF1: =AND(A1<=(TODAY()+60,A1>=TODAY()+20)
CF2: =A1<=(TODAY()+20)

at least for a range for between put the lower number first.
For more information on Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If you are using Conditional Formatting, the change will be immediate.
You had a problem with your formulas (use of TODAY().

 

[David McRitchie]

I don't think she really got any results, at least not what she was
expecting. Worksheet formula would not allow it. Errors in
Conditional Formatting will not produce an error -- that is the nature
of the beast -- it just reduces the entire statement like any other
statement to True or False. In other words it is a good idea
to check such syntax or statements on the worksheet to check
their validity and that they are working as desired.

 

[Peo Sjoblom]

When I try to enter =TODAY(0) under formula is in conditional formatting I
get an error preventing me from doing it