Conditional formating to change dates but also changes text

[roniaelm]

Hi,

I am trying to apply conditional formatting so that any dates that are
equal to or greater then today's date turn red. The conditional
formating works fine (whether I used the Formula Is or Cell Value Is
options as I have provided below) on all dates but the problem is that
it also turns any text I have in the column to red, not just the
dates. I do not want the text to change colour at all.

Formula Is option =$A2>=TODAY()
or
Cell Value Is option Greater then or equal to
=TODAY()

Can you please tell me if I am doing anything wrong or missing
something.

Thanks!

 

[David McRitchie]

=AND(ISNUMBER($A2),$A2>=TODAY())

More information on Conditional Formatting
tp://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[T. Valko]

The problem is that TEXT will evaluate to be greater than *any* number and a
DATE is really just a number formatted to look like a date.

So, if A2 is a text entry like "Joe":

="Joe">=TODAY()

Evaluates to TRUE so the format is applied.

To correct this test that the entry is also a number:

Conditional Formatting
Formula Is: =AND(ISNUMBER($A2),$A2>=TODAY())

Biff

 

[roniaelm]

The problem is that TEXT will evaluate to be greater than *any* number and a
DATE is really just a number formatted to look like a date.

So, if A2 is a text entry like "Joe":

="Joe">=TODAY()

Evaluates to TRUE so the format is applied.

To correct this test that the entry is also a number:

Conditional Formatting
Formula Is: =AND(ISNUMBER($A2),$A2>=TODAY())

Biff










- Show quoted text -

Thank you, you are life savers! That worked!

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thank you, you are life savers! That worked!