Excel 2007 dates and conditional formats

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mary - 20 Nov 2007 21:19 GMT

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

Reply to this Message

Niek Otten - 20 Nov 2007 21:32 GMT

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

Signature

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
[quoted text clipped - 9 lines]
|
| Mary

Reply to this Message

OssieMac - 21 Nov 2007 02:06 GMT

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.

Signature

Regards,

OssieMac

> 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
[quoted text clipped - 9 lines]
>
> Mary

Reply to this Message

Rate this thread: