Count Number of Hours (in a time range) within the time range

[Siops]

Hi,

Good day. I would like to ask some help. I would like to get the number of hours between the Start Time and End Time within the ND Start and ND End Time, and it will fall under "with Hours" column. If none, it will fall under "Excess column". Please see sample below. Hope to hear from you guys soon. Thank you.



[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]ND Start[/TD]
[TD]ND End[/TD]
[TD]within hours[/TD]
[TD]Excess[/TD]
[/TR]
[TR]
[TD]11/2 5 AM[/TD]
[TD]11/2 6 AM[/TD]
[TD]11/1 10 PM[/TD]
[TD]11/2 6 AM[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]11/3 6 AM[/TD]
[TD]11/3 7 AM[/TD]
[TD]11/2 10 PM[/TD]
[TD]11/3 6 AM[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]11/4 8 PM[/TD]
[TD]11/4 8:30 PM[/TD]
[TD]11/4 10 PM[/TD]
[TD]11/5 6 AM[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.5[/TD]
[/TR]
[TR]
[TD]11/4 10:30 PM[/TD]
[TD]11/4 11:30 PM[/TD]
[TD]11/4 10 PM[/TD]
[TD]11/5 6 AM[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]11/2 5 AM[/TD]
[TD]11/2 10 AM[/TD]
[TD]11/1 10 PM[/TD]
[TD]11/2 6 AM[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

Assuming you have valid time/date values in A2:D2 then use this formula in E2

=MAX(0,MIN(B2:D2)-MAX(A2,C2))*24

and this one in F2

=(B2-A2)*24-E2

Format both cell as general or number

 

[Siops]

Hi Barry, thanks for the quick reply. The result is different from the sample i showed.

[TABLE="width: 559"]
<colgroup><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]ND Start[/TD]
[TD]ND End[/TD]
[TD]WITH ND[/TD]
[TD]WITHOUT ND[/TD]
[/TR]
[TR]
[TD="align: right"]11/2/2017 5:00[/TD]
[TD="align: right"]11/2/2017 6:00[/TD]
[TD="align: right"]11/1/2017 22:00[/TD]
[TD="align: right"]11/2/2017 6:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11/3/2017 6:00[/TD]
[TD="align: right"]11/3/2017 7:00[/TD]
[TD="align: right"]11/2/2017 22:00[/TD]
[TD="align: right"]11/3/2017 6:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11/4/2017 20:00[/TD]
[TD="align: right"]11/4/2017 20:30[/TD]
[TD="align: right"]11/4/2017 22:00[/TD]
[TD="align: right"]11/5/2017 6:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]11/4/2017 22:30[/TD]
[TD="align: right"]11/4/2017 23:30[/TD]
[TD="align: right"]11/4/2017 22:00[/TD]
[TD="align: right"]11/5/2017 6:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11/2/2017 5:00[/TD]
[TD="align: right"]11/2/2017 10:00[/TD]
[TD="align: right"]11/1/2017 22:00[/TD]
[TD="align: right"]11/2/2017 6:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

=MAX(0,MIN(B2:D2)-MAX(A2,C2))*24

Apologies, typo in first formula, should be like this

=MAX(0,MIN(B2 , D2)-MAX(A2,C2))*24

 

[Siops]

Thank you so much, Barry. You're the best.