Forum Discussion

Anonymous's avatar
Anonymous
Jul 26, 2018

Calculating Time Difference with Gap

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:2...
BI & Data Analytics
Show and Tell
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 26, 2018

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

 

 

 

Resources