Excel 2007 dates and conditional formats

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

Can anyone please tell me why I can not use a conditional format (<>=)
to identify if a date is greater or smaller then another. Everytime I
try it, Excel does it right in the first column but misses by 2 days
in all following. It also appears to have problems with the years.
Example:
Date of propposed compleation of a task is 10/10/07; if the date of
actual completion is 10/10/07 or sooner, turn GREEN. If the date of
completion is 10/11/07 or older, turn RED.
Can anyone help??

Regards

Mary
 
Hi Mary,

First thing to check:

What does 10/11/07 mean?
November 10 1907?
November 10 2007?
October 11 1907?
October 11 2007?

Note that date formats can differ between worksheets and VBA

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Can anyone please tell me why I can not use a conditional format (<>=)
| to identify if a date is greater or smaller then another. Everytime I
| try it, Excel does it right in the first column but misses by 2 days
| in all following. It also appears to have problems with the years.
| Example:
| Date of propposed compleation of a task is 10/10/07; if the date of
| actual completion is 10/10/07 or sooner, turn GREEN. If the date of
| completion is 10/11/07 or older, turn RED.
| Can anyone help??
|
| Regards
|
| Mary
 
Hi Mary,

I assume that you are putting them in as formulas so try this method:-

=B1<=DATEVALUE("10/10/07")

and the second one as:-

=B1<=DATEVALUE("11/10/07")

However, as Niek says, really need to know what date format you use. I am in
a d/m/y area and 11/10/07 represents 11 Oct 2007 and in the above formula,
the second one overrides the first. Seems to me that the second one should be
9 Oct 2007 so it is Green if met the date and red if it did not.

As a tip: When using conditional format, select the entire range to apply
the conditional format and then apply the formula as if only applying it to
the active cell which is the first one of the selection and remains white
while the rest are highlighted. Excel looks after applying it to the
remainder.
 
Back
Top