Forum Discussion

Deleted's avatar
Deleted
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: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! 

 

BI & Data Analytics
Show and Tell

1 Reply

  • 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