Conditional formatting with time-formats where one cell is a formula

[paulkinzelman]

I'm trying to conditionally format a cell based on the comparison of
two times. I've gotten it to work when both are hard numbers, like comparing 11:00 and 12:00. However, when one of them is the result of a formula, the conditional formatting doesn't seem to work. What am I doing wrong?

I want to color B15 based on the comparison between B15 and L14.

The conditional format formula I have is:
"Formula Is" | "=B15<l14" |="" format="" pattern="" <red="">[less-than]L14" | Format | Pattern | Red
( [less-than] is instead of less-than angle bracket)

The cell L14 has the formula:
=IF(NOT(ISBLANK(E14)),IF(HOUR(E14>14),(E14-TIME(14,0,0)),(E14+TIME(10,0,0))),"")
Basically if E14 is not blank, then L14 is 14 hrs plus E14, plus the wrap over midnight is handled.

In addition, the conditional format I want has the issue of the wrap-over-midnight too, suggestions on how to handle that, so that if L14 is 00:30 and B15 is 23:30, the cell turns red?

TIA!</l14">

 

[paulkinzelman]

I can put this expression into a test cell:
=((HOUR(((L14[less-than]B15)+L14-B15))+MINUTE(((L14[less-than]B15)+L14-B15))/60)<10)<b15)+l14-b15))+minute(((l14<b15)+l14-b15)) 60)<10)

And it goes true and false as it should when I change B15 back and forth
across the "threshold".

But when I put the same expression into a conditional-format formula, it comes out as:
</b15)+l14-b15))+minute(((l14<b15)+l14-b15))>="((HOUR(((L14[less-than]B15)+L14-B15))+MINUTE(((L14[less-than]B15)+L14-B15))/60)<10)"
<b15)+l14-b15))+minute(((l14<b15)+l14-b15)) 60)<10)
<b15)+l14-b15))+minute(((l14<b15)+l14-b15)) 60)<10)"
and when I change B15 back and forth, it seems to never be true, the format
never changes to red.

However, if I try a simple conditional format formula like
=B10<b9
>B9
I can change B10 to be greater and less than B9 and the color changes as it should.

Furthermore, if I evaluate that complex formula in a scratch cell, then set
the conditional format to depend on that cell being true or false, that works too.
I'd really rather not have to evaluate the expression to a scratch cell, it would
be a cumbersome workaround, and it should not be necessary.

Any ideas?

Does the conditional format facility choke on complicated formulas?</b9
</b15)+l14-b15))+minute(((l14<b15)+l14-b15))></b15)+l14-b15))+minute(((l14<b15)+l14-b15))>

 

[T. Valko]

I can put this expression into a test cell:
=((HOUR(((L14[less-than]B15)+L14-B15))+MINUTE(((L14[less-than]B15)+L14-B15))/60)<10)<B15)+L14-B15))+MINUTE(((L14<B15)+L14-B15)) 60)<10)

And it goes true and false as it should when I change B15 back and forth
across the "threshold".

But when I put the same expression into a conditional-format formula, it comes out as:
</B15)+L14-B15))+MINUTE(((L14<B15)+L14-B15))>="((HOUR(((L14[less-than]B15)+L14-B15))+MINUTE(((L14[less-than]B15)+L14-B15))/60)<10)"
<B15)+L14-B15))+MINUTE(((L14<B15)+L14-B15)) 60)<10)
<B15)+L14-B15))+MINUTE(((L14<B15)+L14-B15)) 60)<10)?
and when I change B15 back and forth, it seems to never be true, the format
never changes to red.

However, if I try a simple conditional format formula like
=B10<B9
>B9
I can change B10 to be greater and less than B9 and the color changes as it should.

Furthermore, if I evaluate that complex formula in a scratch cell, then set
the conditional format to depend on that cell being true or false, that works too.
I'd really rather not have to evaluate the expression to a scratch cell, it would
be a cumbersome workaround, and it should not be necessary.

Any ideas?

Does the conditional format facility choke on complicated formulas?</B9
</B15)+L14-B15))+MINUTE(((L14<B15)+L14-B15))></B15)+L14-B15))+MINUTE(((L14<B15)+L14-B15))>

Ok, let's start with this formula that's entered in L14:

=IF(NOT(ISBLANK(E14)),IF(HOUR(E14>14),(E14-TIME(14,0,0)),(E14+TIME(10,0,0))),"")

Not sure what you want to do with that but it can be written like this...

=IF(E14<>"",IF(HOUR(E14)>14,E14-TIME(14,0,0),E14+TIME(10,0,0)),"")

Now, if you want to format B15 then use this formula:

=AND(COUNT(L14),B15 < L14)

When L14 contains the blank and B15 contains a time/number then B15 will evaluate to be less than L14. In Excel numbers have less value than text.

 

[paulkinzelman]

First, your suggestion does simplify one of the formulas, thanks!

I then tried:
=AND(COUNT(B16),AND(COUNT(L15),B16 < L15))
Both B16 and L15 need to be non-blank (thanks for the trick!).

However, this doesn't work when B16 is 23:00 and L15 is 01:00 on the next day. To fix this case, I used:
=AND(COUNT(B16),COUNT(L15),A15=A16,B16<L15)
where A15=A16 determines whether it's the next day or not.

So I think I'm all set! Thanks for your great help!

 

[T. Valko]

First, your suggestion does simplify one of the formulas, thanks!

I then tried:
=AND(COUNT(B16),AND(COUNT(L15),B16 < L15))
Both B16 and L15 need to be non-blank (thanks for the trick!).

However, this doesn't work when B16 is 23:00 and L15 is 01:00 on the next day. To fix this case, I used:
=AND(COUNT(B16),COUNT(L15),A15=A16,B16<L15)
where A15=A16 determines whether it's the next day or not.

So I think I'm all set! Thanks for your great help!

Good deal. Thanks for the feedback!

 

[paulkinzelman]

I'm mainly doing it for myself, so when I forget the answer, I can come back here and find it again.