Calculating Time Difference with Gap

[Deleted]

Hello,

I want to calculate the time difference (duration) between a start and end time. It should not count between the hours of 10PM-5:29AM. However, if the start time does fall between 10PM-5:29AM, the time difference will calculate beginning from 5:30AM. If the start time and end time are both between 10PM-5:29AM it is considered negative or zero if too complicated. Duration in hours is fine too. Explanation of solution would be helpful too! Examples below - expected results are bolded:

Start time: 7/18/18 22:33

End time: 7/19/18 10:42

Time duration: 04:42 (2h 12mins)

Start time: 7/18/18 06:30

End time: 7/18/18 08:30

Time duration: 02:00 (2h)

Start time: 7/19/18 0:22

End time: 7/24/18 12:11

Time duration: 72:11 (72h 11mins)

Start time: 7/17/18 23:06

End time: 7/18/18 02:36

Time duration: -03:30 (negative 3h 30mins) or 0

Thanks! 

[#2]

Hi Michael,

I didn't catch how you received the results (in bold) for the first and third examples. Anyway, the formula for such data structure

could be

=MIN(MAX(C2,INT(C2)+5.5/24),INT(C2)+22/24)-
 MIN(MAX(B2,INT(B2)+5.5/24),INT(B2)+22/24)-
   (INT(MIN(MAX(C2,INT(C2)+5.5/24),INT(C2)+22/24))-
   INT(MIN(MAX(B2,INT(B2)+5.5/24),INT(B2)+22/24))
   )*7.5/24

and attached