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

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...

"Deanna" wrote:

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

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

Jim Thomlinson


"Deanna" wrote:

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...

"Deanna" wrote:

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

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

Anyone have any suggestions?

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Deanna" wrote:

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...

"Deanna" wrote:

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

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

"Deanna" wrote in message
...
So how do I change the statements to avoid the blank cell=1/1/1900 issue?

Anyone have any suggestions?

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Deanna" wrote:

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...

"Deanna" wrote:

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

Thanks! That worked.

"David Biddulph" wrote:

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

"Deanna" wrote in message
...
So how do I change the statements to avoid the blank cell=1/1/1900 issue?

Anyone have any suggestions?

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Deanna" wrote:

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...

"Deanna" wrote:

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