Calculate seconds between timestamps with time conditions

[Testeri]

Hi,

I have a table for events with start and end time. I want to calculate how many seconds there are for each work shift between those times. I want to make calculated columns for each shift (Morning shift, evening shift, night shift). Event duration can be from a few seconds to days.

For example I have timestamps 1.1.2023 10:23:45 and 4.1.2023 22:56:22 for a event.

Morning shift column: total of seconds that are between 06:00:00 and 13:59:59

Evening shift column: total of seconds that are between 14:00:00 and 21:59:59

Night shift column: total of seconds that are between 22:00:00 and 05:59:59

Does anyone have ideas for this? Hope I was clear enough.

[BeaBF]

@Testeri Hi!

Morning Shift =
VAR Start = Events[Start Time]
VAR End = Events[End Time]
VAR ShiftStart = TIME(6,0,0)
VAR ShiftEnd = TIME(14,0,0)
VAR Duration = IF(End >= DATEADD(Start, 1, DAY), TIME(24,0,0), End - Start)
VAR ShiftDuration = IF(Start < DATEADD(Start, 1, DAY), MIN(TIME(8,0,0), ShiftEnd - Start), 0)
VAR Overlap = IF(Start < DATEADD(Start, 1, DAY), MAX(MIN(ShiftEnd, End) - ShiftStart, 0), 0)
RETURN IF(Duration > 0, IF(Overlap > 0, Overlap, ShiftDuration), 0)

Evening Shift =
VAR Start = Events[Start Time]
VAR End = Events[End Time]
VAR ShiftStart = TIME(14,0,0)
VAR ShiftEnd = TIME(22,0,0)
VAR Duration = IF(End >= DATEADD(Start, 1, DAY), TIME(24,0,0), End - Start)
VAR ShiftDuration = IF(Start < DATEADD(Start, 1, DAY), MIN(TIME(8,0,0), ShiftEnd - MAX(Start, ShiftStart)), 0)
VAR Overlap = IF(Start < DATEADD(Start, 1, DAY), MAX(MIN(ShiftEnd, End) - MAX(Start, ShiftStart), 0), 0)
RETURN IF(Duration > 0, IF(Overlap > 0, Overlap, ShiftDuration), 0)

Night Shift =
VAR Start = Events[Start Time]
VAR End = Events[End Time]
VAR ShiftStart = TIME(22,0,0)
VAR ShiftEnd = TIME(6,0,0)
VAR Duration = IF(End >= DATEADD(Start, 1, DAY), TIME(24,0,0), End - Start)
VAR ShiftDuration = IF(Start < DATEADD(Start, 1, DAY), MIN(TIME(9,0,0), End - MAX(Start, ShiftStart)), 0)
VAR Overlap = IF(Start < DATEADD(Start, 1, DAY), MAX(MIN(ShiftEnd, End) - MAX(Start, ShiftStart), 0), 0)
RETURN IF(Duration > 0, IF(Overlap > 0, Overlap, ShiftDuration), 0)

This code assumes that the Start Time and End Time columns in the Events table are of type DateTime

BBF

[Testeri]

Hi, thanks for fast reply. I tried that calculation for morning shift but got error "The first argument to DATEADD must specify a column." Start time and end time columns are type of Date/time.

[BeaBF]

@Testeri Try this:

Morning Shift =
VAR StartTime = Events[Start Time]
VAR EndTime = Events[End Time]
VAR MorningStart = TIME(6, 0, 0)
VAR MorningEnd = TIME(14, 0, 0)
VAR ShiftStart = IF(StartTime > MorningStart, StartTime, MorningStart)
VAR ShiftEnd = IF(EndTime < MorningEnd, EndTime, MorningEnd)
RETURN IF(ShiftStart >= ShiftEnd, 0, SHIFTDIFF(ShiftStart, ShiftEnd, SECOND))

BBF

[Testeri]

Hi @BeaBF , got only zero seconds with that for every row and every shift type. See attached example. In that case value for evening shift should be 525 seconds because start and end times are in same day and between 14:00 and 22:00. Your example had function "SHIFTDIFF", should that be DATEDIFF?