Calculating difference in time - conditional formatting

[Mux99]

Hi all

I would like to calculate Time-in - Time-out and if there is less than a 9 hour difference, highlight the cells. In the example below B4:B5 & C2:C3 would be highlighted.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Time-in[/TD]
[TD]Time-out[/TD]
[TD]Time-in[/TD]
[TD]Time-out[/TD]
[TD]Time-in[/TD]
[TD]Time-out[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]18:00[/TD]
[TD]09:00[/TD]
[TD]22:00[/TD]
[TD]06:00[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]12:00[/TD]
[TD]23:00[/TD]
[TD]07:00[/TD]
[TD]16:00[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

Not quite sure about your example

In the example below B4:B5 & C2:C3 would be highlighted.

Where are all these cells exactly ?

 

[Mux99]

Hi,

Not quite sure about your example

Where are all these cells exactly ?

Sorry about that as I did not copy the whole table. I meant to say D2:E2 & B3:C3. Please see the example below

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Time-in[/TD]
[TD]Time-out[/TD]
[TD]Time-in[/TD]
[TD]Time-out[/TD]
[TD]Time-in[/TD]
[TD]Time-out[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]08:00[/TD]
[TD]18:00[/TD]
[TD]09:00[/TD]
[TD]22:00[/TD]
[TD]06:00[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]12:00[/TD]
[TD]23:00[/TD]
[TD]07:00[/TD]
[TD]16:00[/TD]
[TD]10:00[/TD]
[TD]19:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Thanks for your clarification ...

For your range B2:F3 you can have following formula

=MOD(B2-A2,1)<0.375

Hope this will help