Conditional Formatting Applied when Statement Not True
 

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

Conditional Formatting Applied when Statement Not True
 

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

Conditional Formatting Applied when Statement Not True
 

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

Conditional Formatting Applied when Statement Not True
 

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

Conditional Formatting Applied when Statement Not True
 

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

Conditional Formatting Applied when Statement Not True
 

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

Conditional Formatting Applied when Statement Not True
 

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.

"Deanna" wrote:

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

Conditional Formatting Applied when Statement Not True
 

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

"Deanna" wrote in message
...
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.

"Deanna" wrote:

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