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

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$4TIME(14,0,0)


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

Thanks

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

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



--
Biff
Microsoft Excel MVP


"Waitsu" wrote in message
...
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$4TIME(14,0,0)


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

Thanks

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

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$5TIME(7,0,0))


Regards,

OssieMac




"Waitsu" wrote:

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$4TIME(14,0,0)


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

Thanks

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

Thanks - this seems to have solved my problem.

"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"Waitsu" wrote in message
...
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$4TIME(14,0,0)


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

Thanks

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Waitsu" wrote in message
...
Thanks - this seems to have solved my problem.

"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"Waitsu" wrote in message
...
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$4TIME(14,0,0)


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

Thanks