Automatic updating of Conditional Formats using dates
 

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?

--
Thank you for your help

Marylin

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().
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"MAD" wrote in message ...
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?

--
Thank you for your help

Marylin

set calculations to automatic under toolsoptionscalculations

--
Regards,

Peo Sjoblom


"MAD" wrote in message
...
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?

--
Thank you for your help

Marylin

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


"David McRitchie" wrote in message
...
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().
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"MAD" wrote in message
...
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?

--
Thank you for your help

Marylin

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" wrote in messagel...
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?

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

--
Regards,

Peo Sjoblom


"David McRitchie" wrote in message
...
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" wrote in messagel...
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?