Calculate overlapping hours between two Start End times

[Msadiqrajani]

Hi,

I have Data with start end Time in 24+ hours format like.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]StartTime[/TD]
[TD]End Time[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]4:30[/TD]
[/TR]
[TR]
[TD]8:30[/TD]
[TD]15:00[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]20:00[/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD]25:30[/TD]
[/TR]
[TR]
[TD]27:00[/TD]
[TD]32:00[/TD]
[/TR]
[TR]
[TD]34:00[/TD]
[TD]44:00[/TD]
[/TR]
</tbody>[/TABLE]

Similar to above, I have one more time range like
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Time[/TD]
[TD]10:00[/TD]
[/TR]
[TR]
[TD]End Time[/TD]
[TD]40:00[/TD]
[/TR]
</tbody>[/TABLE]

I want to calculate Time ranges, so I will get result like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]StartTime[/TD]
[TD]End Time[/TD]
[TD]Over Lap Time[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]4:30[/TD]
[TD]0:00[/TD]
[/TR]
[TR]
[TD]8:30[/TD]
[TD]15:00[/TD]
[TD]5:00[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]20:00[/TD]
[TD]5:00[/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD]25:30[/TD]
[TD]5:30[/TD]
[/TR]
[TR]
[TD]27:00[/TD]
[TD]32:00[/TD]
[TD]5:00[/TD]
[/TR]
[TR]
[TD]34:00[/TD]
[TD]44:00[/TD]
[TD]6:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[ITWare2008]

Hi,

Could you explain how do you achieved those over lap time

Thanks in advance


Vândalo

 

[Msadiqrajani]

I have Data with start end Time in 24+ hours format like.
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]StartTime[/TD]
[TD]End Time[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]4:30[/TD]
[/TR]
[TR]
[TD]8:30[/TD]
[TD]15:00[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]20:00[/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD]25:30[/TD]
[/TR]
[TR]
[TD]27:00[/TD]
[TD]32:00[/TD]
[/TR]
[TR]
[TD]34:00[/TD]
[TD]44:00[/TD]
[/TR]
</tbody>[/TABLE]


Similar to above, I have one more time range like
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Start Time[/TD]
[TD]10:00[/TD]
[/TR]
[TR]
[TD]End Time[/TD]
[TD]40:00[/TD]
[/TR]
</tbody>[/TABLE]


I want to calculate Time ranges, so I will get result like this:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]StartTime[/TD]
[TD]End Time[/TD]
[TD]Over Lap Time[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]4:30[/TD]
[TD]0:00

Time Is not overlapping with Start(10:00) and end(40:00)
So its 0:00[/TD]
[/TR]
[TR]
[TD]8:30[/TD]
[TD]15:00[/TD]
[TD]5:00

Hours 10:00 to 15:00 are overlapping with Start(10:00) and end(40:00)
so its 5:00[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]20:00[/TD]
[TD]5:00

Hours 15:00 to 20:00 are overlapping with Start(10:00) and end(40:00)
so its 5:00[/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD]25:30[/TD]
[TD]5:30

Hours 20:00 to 25:30 are overlapping with Start(10:00) and end(40:00)
so its 5:30[/TD]
[/TR]
[TR]
[TD]27:00[/TD]
[TD]32:00[/TD]
[TD]5:00

Hours 27:00 to 32:00 are overlapping with Start(10:00) and end(40:00)
so its 5:00[/TD]
[/TR]
[TR]
[TD]34:00[/TD]
[TD]44:00[/TD]
[TD]6:00

Hours 34:00 to 40:00 are overlapping with Start(10:00) and end(40:00)
so its 6:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[ITWare2008]

Hi,

Using this formula I got your results.

=IF(N6<$M$3,0,IF(N6>$N$3,N6-M6-(N6-$N$3),IF(M6<$M$3,N6-M6-($M$3-M6),N6-M6)))

M3 -> Your 10:00
N3 -> Your 40:00

N6 and M6 are your varying time values

Vândalo

 

[Msadiqrajani]

I tried this formula..But got few strange results
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]BreakStart[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]BreakEnd[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]9:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]9:30 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]Session[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]Session Start[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]Session End[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]Time Overlap[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]7 :00 Am[/TD]
[TD]7:45 AM[/TD]
[TD]0:00

Its Correct[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]=IF(C4<$B$2,0,IF(C4>$C$2,C4-B4-(C4-$C$2),IF(B4<$B$2,C4-B4-($B$2-B4),C4-B4)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]8:00 Am[/TD]
[TD]8:45 AM[/TD]
[TD]1:30

Its Strange[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]=IF(C5<$B$2,0,IF(C5>$C$2,C5-B5-(C5-$C$2),IF(B5<$B$2,C5-B5-($B$2-B5),C5-B5)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]9:00 AM[/TD]
[TD]9:45 AM[/TD]
[TD]0:30

Its Correct[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]=IF(C6<$B$2,0,IF(C6>$C$2,C6-B6-(C6-$C$2),IF(B6<$B$2,C6-B6-($B$2-B6),C6-B6)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]S4[/TD]
[TD]10:00 AM[/TD]
[TD]10:45 AM[/TD]
[TD]#####



instead of 0value...Why negativeIts Time negative[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]=IF(C7<$B$2,0,IF(C7>$C$2,C7-B7-(C7-$C$2),IF(B7<$B$2,C7-B7-($B$2-B7),C7-B7)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]S5[/TD]
[TD]11:00 AM[/TD]
[TD]11:45 AM[/TD]
[TD]#####

Same here as above.[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]=IF(C8<$B$2,0,IF(C8>$C$2,C8-B8-(C8-$C$2),IF(B8<$B$2,C8-B8-($B$2-B8),C8-B8)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]