Calculating duration from two timestamps based on schedule

[Digadumm]

Hi!

I have two date/time columns defining Start and End -times. What I need is to get a duration -info based on following schedules:

DaySchedule = 08:00-22:00
NightSchedule=22:00-08:00

so as an example:

StartTime= 1.1.2019 21:30
EndTime= 2.1.2019 03:30

Result should be shown so that I have a duration from those timestamps for daytime and nighttime in own columns - like

DayTime=30 minutes
NightTime= 5h 30 minutes

Is there an easy way to do this?

[v-chuncz-msft]

@Digadumm 

You may download the file attached and check the steps in Query Editor.

[Digadumm]

Hi!

That seem to work - almost. Only issue is that I have actually also seconds on my timestamps, so in some cases that causes a negative value as a result to nighttime - as you see on the example below. How to correct that one?