Conditional formating to change dates but also changes text

R

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!
 
T

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
 
R

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 -
Click to expand...

Thank you, you are life savers! That worked!
 
Top