Solved: Calculate working hours between two dates

assemomar · ‎03-19-2025

Calculate working hours between two dates

I'm trying to calculate the number of working hours/minutes (start hours start 7 AM , end hour 17 PM) between two dates (ie. excluding non-working hours and weekends is Friday).

This is the basic format - and I'm trying to find out how long it takes for an issue to get resolved, in working hours/minutes. Can anyone help, please?

Issue id Created Resolved
1265400 03/03/2025 13:54 03/03/2025 14:21
1265256 03/03/2025 12:54 03/03/2025 13:45
1263801 03/03/2025 09:09 03/03/2025 09:11
1263789 03/03/2025 08:42 03/03/2025 10:50
1262321 02/03/2025 17:12 03/03/2025 09:18
1261985 02/03/2025 11:54 02/03/2025 11:57
1258735 28/02/2025 22:27 02/03/2025 16:53
1258687 28/02/2025 16:26 03/03/2025 10:12
1258638 28/02/2025 14:54 02/03/2025 16:00

prasannagoloju · ‎03-19-2025

Hi @assemomar

Working_Hours =
VAR StartDateTime = 'Table'[Created]
VAR EndDateTime = 'Table'[Resolved]
VAR StartDate = DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime))
VAR EndDate = DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime))
VAR WorkStart = TIME(7, 0, 0)
VAR WorkEnd = TIME(17, 0, 0)
VAR TotalWorkHours =
SUMX(
FILTER(
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"IsWorkday", WEEKDAY([Date], 2) <> 5
),
[IsWorkday] = TRUE
),
VAR CurrentDate = [Date]
VAR StartHour = IF(CurrentDate = StartDate, MAX(WorkStart, TIME(HOUR(StartDateTime), MINUTE(StartDateTime), 0)), WorkStart)
VAR EndHour = IF(CurrentDate = EndDate, MIN(WorkEnd, TIME(HOUR(EndDateTime), MINUTE(EndDateTime), 0)), WorkEnd)
RETURN MAX(0, DATEDIFF(StartHour, EndHour, MINUTE) / 60.0)
)

RETURN ROUND(TotalWorkHours, 2)

View solution in original post

Khushidesai0109 · ‎03-19-2025

Hiii @assemomar

WorkingHours =
VAR StartDateTime = 'Issues'[Created]
VAR EndDateTime = 'Issues'[Resolved]

-- Define working hours
VAR WorkdayStart = TIME(7, 0, 0) -- 07:00 AM
VAR WorkdayEnd = TIME(17, 0, 0) -- 05:00 PM

-- Extract date and time components
VAR StartDate = DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime))
VAR EndDate = DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime))

VAR StartTime = TIME(HOUR(StartDateTime), MINUTE(StartDateTime), SECOND(StartDateTime))
VAR EndTime = TIME(HOUR(EndDateTime), MINUTE(EndDateTime), SECOND(EndDateTime))

-- Count working days excluding Fridays (Friday = 5 in DAX)
VAR WorkingDays =
COUNTX(
FILTER(
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"Weekday", WEEKDAY([Date], 2) -- 1=Monday, 7=Sunday (Friday=5)
),
[Weekday] <> 5
),
[Date]
)

-- Adjust for first day
VAR FirstDayHours =
IF(
WEEKDAY(StartDate, 2) <> 5, -- If not Friday
DATEDIFF(
MAX(StartTime, WorkdayStart),
MIN(WorkdayEnd, EndTime),
MINUTE
) / 60,
0
)

-- Adjust for last day
VAR LastDayHours =
IF(
WEEKDAY(EndDate, 2) <> 5, -- If not Friday
DATEDIFF(
WorkdayStart,
MIN(WorkdayEnd, EndTime),
MINUTE
) / 60,
0
)

-- Adjust for full working days
VAR FullDayHours = (WorkingDays - 2) * 10 -- Each full working day contributes 10 hours

-- Compute total working hours
VAR TotalWorkingHours = FirstDayHours + LastDayHours + FullDayHours

RETURN TotalWorkingHours

If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

View solution in original post

assemomar · ‎03-19-2025

.

Khushidesai0109 · ‎03-19-2025