Calculate overlapping hours between two Start End times

[Msadiqrajani]

Hi,

I have Data with start end Time in 24+ hours format like.

StartTime	End Time
00:00	4:30
8:30	15:00
15:00	20:00
20:00	25:30
27:00	32:00
34:00	44:00

<tbody>
</tbody>

Similar to above, I have one more time range like

Start Time	10:00
End Time	40:00

<tbody>
</tbody>

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

StartTime	End Time	Over Lap Time
00:00	4:30	0:00
8:30	15:00	5:00
15:00	20:00	5:00
20:00	25:30	5:30
27:00	32:00	5:00
34:00	44:00	6:00

<tbody>
</tbody>

 

[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.

StartTime	End Time
00:00	4:30
8:30	15:00
15:00	20:00
20:00	25:30
27:00	32:00
34:00	44:00

<tbody>
</tbody>

Similar to above, I have one more time range like

Start Time	10:00
End Time	40:00

<tbody>
</tbody>

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

StartTime	End Time	Over Lap Time
00:00	4:30	0:00 Time Is not overlapping with Start(10:00) and end(40:00) So its 0:00
8:30	15:00	5:00 Hours 10:00 to 15:00 are overlapping with Start(10:00) and end(40:00) so its 5:00
15:00	20:00	5:00 Hours 15:00 to 20:00 are overlapping with Start(10:00) and end(40:00) so its 5:00
20:00	25:30	5:30 Hours 20:00 to 25:30 are overlapping with Start(10:00) and end(40:00) so its 5:30
27:00	32:00	5:00 Hours 27:00 to 32:00 are overlapping with Start(10:00) and end(40:00) so its 5:00
34:00	44:00	6:00 Hours 34:00 to 40:00 are overlapping with Start(10:00) and end(40:00) so its 6:00

<tbody>
</tbody>

 

[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

BreakStart <tbody> </tbody>	BreakEnd <tbody> </tbody>
9:00 AM <tbody> </tbody>	9:30 AM <tbody> </tbody>
Session <tbody> </tbody>	Session Start <tbody> </tbody>	Session End <tbody> </tbody>	Time Overlap <tbody> </tbody>	Formula
S1	7 :00 Am	7:45 AM	0:00 Its Correct	=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))) <tbody> </tbody>
S2	8:00 Am	8:45 AM	1:30 Its Strange	=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))) <tbody> </tbody>
S3	9:00 AM	9:45 AM	0:30 Its Correct	=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))) <tbody> </tbody>
S4	10:00 AM	10:45 AM	##### instead of 0value...Why negativeIts Time negative	=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))) <tbody> </tbody>
S5	11:00 AM	11:45 AM	##### Same here as above.	=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))) <tbody> </tbody>

<tbody>
</tbody>