Conditional Format If End Time & Start Time Is Less Than 11

[StokieWorm]

Hi,

I am trying to figure out the formula to conditionally format a cell based on total hours between an end time and a start time.
The formula needs to work between 20:45 & 07:15 for this example, and if the time between them two times is less than 11 hours then format the cell colour to red.

E2 - M Shift ( Cell That Needs Formatting )
U2 - 20:45 ( End Time )
W2 - 07:15 ( Start Time )

Thanks in advance
StokieWorm

[63falcondude]

Highlight E2 > Conditional Formatting > New Rule > Use a formula

=(U2-W2)*24<11

Format: Fill color of your choice > OK > OK

[StokieWorm]

Thank you for the quick reply,

Can i just ask this question, If the (End Time) is on the evening of the day before the (Start Time) is the formula slightly different and not multiplied by 24 hours?

Regards StokieWorm

[bebo021999]

Try:
=(U2-W2+(U2<W2))<11/24

[63falcondude]

If it is possible to have a start and end time on different days, then you will have to specify the days as well.

I would put the date and time in the same cell but you could make this calculation if they were in different cells.