Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Testeri
Advocate I
Advocate I

Calculate seconds between timestamps with time conditions

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.

4 REPLIES 4
BeaBF
Super User
Super User

@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

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.

@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

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?

 

Shifttime.jpg

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.