Conditional Formatting Applied when Statement Not True

[Deanna]

I have the following for a conditional formatting statement for a row

Formula Is =AND(ISBLANK)$L7),(TODAY()>$J7))

in order to highlight the row yellow when the Date Closed cell (L7) for the
item is blank and the Closure Due Date (J7) has passed.

The row is highlighting when it is completely blank, with no data filled in.
I have only the headers typed in the appropriate columns, and am using the
format painter to format all my rows, and they are all highlighting yellow...

I have two other conditions set up with two other colors. The one giving me
the issue is Condition 2.

Condition 1:
Formula Is =$L7<>""
So that the row highlights grey once the item has a Date Closed entered.

Condition 3:
Formula Is =OR($F7>(TODAY()+730),ISBLANK($F7))
So that the row highlights orange if the last time the item has been
serviced was more than two years ago or there is no record of when it was
last serviced (F7 being the date of last service).

Any idea why Condition 2 is coming up True, and highlighting yellow, when
the row is completely blank and there is no information to determine if the
condition is True or False?

Thanks,
Deanna

 

[Deanna]

To add to my quandry, I changed Condition 2 to

Formula IS =AND(ISBLANK($L7),(TODAY()>$J7),ISTEXT($A7))

in order to see if the row loses the yellow highlight. It does lose the
yellow, and turns orange, which means it's reading Condition 3 to be True
when there is no information in the cells for that condition. Once I enter
info into A7, the row is yellow, even though I have not entered any dates for
any of the conditions.

Is Excel reading a blank cell as 1/1/1900? Which would definitely make
Conditions 2 and 3 True even if there is no data entered in the appropriate
cell...

 

[Jim Thomlinson]

A blank cell is the same as 0 for the purpose of calculation in this case so
yes you are getting 1/1/1900.

 

[Deanna]

So how do I change the statements to avoid the blank cell=1/1/1900 issue?

Anyone have any suggestions?

 

[David Biddulph]

If your problem is when J7 is blank, you could change
=AND(ISBLANK($L7),(TODAY()>$J7))
to
=AND(ISBLANK($L7),TODAY()>$J7,$J7<>"")

 

[Deanna]

Thanks! That worked.

If your problem is when J7 is blank, you could change
=AND(ISBLANK($L7),(TODAY()>$J7))
to
=AND(ISBLANK($L7),TODAY()>$J7,$J7<>"")

 

[Deanna]

So...would I need to put something to the equivalent in this statement?

=OR($F7<(TODAY()-730),ISBLANK9$F7))

I want the row to highlight if the date of last service is more than two
years ago OR if the date of last service is unknown (in which case the cell
(F7) would be blank). However, the cell would also be blank if there is no
entry on that row at all. Since it's an OR statement, I didn't think the
same solution you gave me in the last post would apply.

 

[David Biddulph]

I assume that by
=OR($F7<(TODAY()-730),ISBLANK9$F7))
you intended to say
=OR($F7<(TODAY()-730),ISBLANK($F7)) ?

In which case, no, I don't think you need that, because if F7 is blank it
will be less than TODAY()-730, assuming that today's date is more than 2
years on from the origin of Excel's date system (which was either 1900 or
1904 depending on which option's you've chosen).

Note also that if I were testing for blanks I would prefer generally to use
=F7="" rather than =ISBLANK(F7), as the latter will not be true if F7
contains the empty text string ="", and =$F7<(TODAY()-730) is not true if F7
is a string instead of being truly empty.