Time - How do you see if a time value is between 2 values?

[Waitsu]

I want to perform a conditional formating in Excel that says:

If the time is between 07:00 and 13:59 then turn the cell border red and
turn the font red.

if the time is between 14:00 and 07:00 turn the cell green.
Condition 1
This works to turn the cell green: =$F$4<=$E$4

Condition 2
This works to change the font red: =$F$4>=$E$4

Condition 3
This does not turn the cell green: =$F$4>TIME(14,0,0)


Does anyone have any idea on how can I code this so that it will work?

Thanks

 

[T. Valko]

The problem is probably because either condition 1 or condition 2 (one or
the other) is always being met so condition 3 NEVER gets evaluated.

You have an overlap in your conditions at 7:00. You only need 2 conditions.

Try these:

Condition 1 - GREEN (between 14:00 and 6:59 inclusive)

=AND(ISNUMBER(cell_ref),OR(cell_ref>=TIME(14,0,0),cell_ref<TIME(7,0,0)))

Condition 2 - RED (between 7:00 and 13:59 inclusive)

=AND(cell_ref>=TIME(7,0,0),cell_ref<=TIME(13,59,0))

 

[OssieMac]

Not sure that I really understand but this might put you on the right track
when trying to format based on between 2 values using AND function:-

Example1:-
=AND($E$5<$E$4,$E$5>$F$4)


Example2:-
=AND($F$5<$E$4,$F$5>TIME(7,0,0))


Regards,

OssieMac

 

[Waitsu]

Thanks - this seems to have solved my problem.

The problem is probably because either condition 1 or condition 2 (one or
the other) is always being met so condition 3 NEVER gets evaluated.

You have an overlap in your conditions at 7:00. You only need 2 conditions.

Try these:

Condition 1 - GREEN (between 14:00 and 6:59 inclusive)

=AND(ISNUMBER(cell_ref),OR(cell_ref>=TIME(14,0,0),cell_ref<TIME(7,0,0)))

Condition 2 - RED (between 7:00 and 13:59 inclusive)

=AND(cell_ref>=TIME(7,0,0),cell_ref<=TIME(13,59,0))

 

[T. Valko]

You're welcome. Thanks for the feedback!