Forum Discussion
Calculate working hours between two dates
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
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)
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 + FullDayHoursRETURN TotalWorkingHours
If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
8 Replies
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)
Hi @prasannagoloju
Thank you for your good help, this code worked OK.
Appreciated.
assemomar have you look at this post Solved: Duration In Business Hours Only - Microsoft Fabric Community
Hi assemomar
Please try using below formulaWorking Hours =
VAR StartDateTime = TableName[Created]
VAR EndDateTime = TableName[Resolved]-- Define Working Hours
VAR StartTime = TIME(7, 0, 0)
VAR EndTime = TIME(17, 0, 0)-- Calculate Total Working Days (Excluding Fridays)
VAR TotalDays = DATEDIFF(StartDateTime, EndDateTime, DAY)
VAR WorkingDays = COUNTROWS(
FILTER(
ADDCOLUMNS(CALENDAR(StartDateTime, EndDateTime),
"DayOfWeek", WEEKDAY([Date], 1)),
[DayOfWeek] <> 6 -- Assuming Friday is day 6
)
)-- Calculate First and Last Day Hours
VAR StartDayHours = MAX(0, MIN(EndTime, TIME(HOUR(StartDateTime), MINUTE(StartDateTime), 0)) - StartTime)
VAR EndDayHours = MAX(0, EndTime - MAX(StartTime, TIME(HOUR(EndDateTime), MINUTE(EndDateTime), 0)))-- Total Hours Calculation
RETURN
IF(
StartDateTime >= EndDateTime,
0,
StartDayHours + EndDayHours + (WorkingDays - 1) * 10
)
Best Regards
Govind Sapkade ( Microsoft Certified Data Analyst , PL 300 Certified , MS Fabric Enthusiast)
Let's Connect
Linkdin - www.linkedin.com/in/govind-sapkade-845104225
Youtube - http://www.youtube.com/@govind_dataanalystDear govind_021
error apprear as following below screenshot :
The start date in Calendar function can not be later than the end date.
Please if you could help me how to solve this as I have many dates as :
Created Resolved
01/01/2024 21:00 20/03/2025 9:09
27/01/2024 7:07 20/03/2025 9:09
19/02/2024 8:18 20/03/2025 9:09
16/03/2024 17:28 20/03/2025 9:09
03/04/2024 5:36 20/03/2025 9:09
07/04/2024 9:14 20/03/2025 9:09
08/05/2024 11:42 20/03/2025 9:09
13/05/2024 12:31 20/03/2025 9:09
27/05/2024 6:01 20/03/2025 9:09
27/05/2024 6:03 20/03/2025 9:09
04/06/2024 15:56 20/03/2025 9:09
19/06/2024 13:42 20/03/2025 9:09
26/06/2024 10:37 20/03/2025 9:09
27/06/2024 6:56 20/03/2025 9:09
02/07/2024 10:21 20/03/2025 9:09
13/07/2024 12:28 20/03/2025 9:09
21/07/2024 8:07 20/03/2025 9:09
01/08/2024 7:31 20/03/2025 9:09
05/08/2024 10:07 20/03/2025 9:09
10/08/2024 3:58 20/03/2025 9:09
13/08/2024 8:19 20/03/2025 9:09
15/08/2024 7:48 20/03/2025 9:09
15/08/2024 12:26 20/03/2025 9:09
01/09/2024 14:35 20/03/2025 9:09
22/09/2024 9:00 20/03/2025 9:09
03/10/2024 6:06 20/03/2025 9:09
05/10/2024 6:38 20/03/2025 9:09
07/10/2024 9:46 20/03/2025 9:09
09/10/2024 7:42 20/03/2025 9:09
21/10/2024 13:10 20/03/2025 9:09
26/10/2024 14:17 20/03/2025 9:09
27/10/2024 7:23 20/03/2025 9:09
27/10/2024 7:35 20/03/2025 9:09
28/10/2024 7:48 20/03/2025 9:09
28/10/2024 13:15 20/03/2025 9:09
29/10/2024 6:36 20/03/2025 9:09
31/10/2024 5:28 20/03/2025 9:09
03/11/2024 6:39 20/03/2025 9:09
05/11/2024 8:18 20/03/2025 9:09
05/11/2024 8:21 20/03/2025 9:09
14/11/2024 7:44 20/03/2025 9:09
20/11/2024 7:44 20/03/2025 9:09
28/11/2024 11:07 20/03/2025 9:09
28/11/2024 4:59 20/03/2025 9:09
02/12/2024 6:38 20/03/2025 9:09
02/12/2024 6:40 20/03/2025 9:09
05/12/2024 7:47 20/03/2025 9:09
09/12/2024 11:55 20/03/2025 9:09
12/12/2024 4:51 20/03/2025 9:09
23/12/2024 8:39 20/03/2025 9:09
01/01/2025 6:44 20/03/2025 9:09
02/01/2025 4:54 20/03/2025 9:09
04/01/2025 7:58 20/03/2025 9:09
05/01/2025 6:58 20/03/2025 9:09
05/01/2025 11:12 20/03/2025 9:09
05/01/2025 13:19 20/03/2025 9:09
06/01/2025 7:51 20/03/2025 9:09
06/01/2025 7:58 20/03/2025 9:09
09/01/2025 5:50 20/03/2025 9:09
11/01/2025 8:56 20/03/2025 9:09
11/01/2025 6:05 20/03/2025 9:09
12/01/2025 10:16 20/03/2025 9:09
17/01/2025 5:53 20/03/2025 9:09
17/01/2025 7:03 20/03/2025 9:09
18/01/2025 8:50 20/03/2025 9:09
18/01/2025 13:16 20/03/2025 9:09
20/01/2025 5:33 20/03/2025 9:09
21/01/2025 11:19 20/03/2025 9:09
21/01/2025 12:10 20/03/2025 9:09
22/01/2025 7:57 20/03/2025 9:09
21/01/2025 7:16 20/03/2025 9:09
23/01/2025 10:37 20/03/2025 9:09
23/01/2025 9:17 20/03/2025 9:09
26/01/2025 6:14 20/03/2025 9:09
27/01/2025 7:38 20/03/2025 9:09
27/01/2025 12:08 20/03/2025 9:09
27/01/2025 12:11 20/03/2025 9:09
27/01/2025 12:18 20/03/2025 9:09
27/01/2025 12:19 20/03/2025 9:09
26/01/2025 23:19 20/03/2025 9:09
30/01/2025 8:48 20/03/2025 9:09
30/01/2025 9:03 20/03/2025 9:09
01/02/2025 14:19 20/03/2025 9:09
02/02/2025 6:12 20/03/2025 9:09
02/02/2025 8:50 20/03/2025 9:09
02/02/2025 8:52 20/03/2025 9:09
02/02/2025 8:55 20/03/2025 9:09
01/02/2025 10:27 20/03/2025 9:09
02/02/2025 11:43 20/03/2025 9:09
04/02/2025 19:24 20/03/2025 9:09
04/02/2025 12:53 20/03/2025 9:09
05/02/2025 8:14 20/03/2025 9:09
05/02/2025 8:56 20/03/2025 9:09
05/02/2025 7:16 20/03/2025 9:09
08/02/2025 11:53 20/03/2025 9:09
08/02/2025 11:25 20/03/2025 9:09
08/02/2025 11:32 20/03/2025 9:09
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 + FullDayHoursRETURN TotalWorkingHours
If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!Dear @Khushidesai0109
error apprear as following below screenshot :
The start date in Calendar function can not be later than the end date.
Please if you could help me how to solve this as I have many dates as :
Created Resolved
01/01/2024 21:00 20/03/2025 9:09
27/01/2024 7:07 20/03/2025 9:09
19/02/2024 8:18 20/03/2025 9:09
16/03/2024 17:28 20/03/2025 9:09
03/04/2024 5:36 20/03/2025 9:09
07/04/2024 9:14 20/03/2025 9:09
08/05/2024 11:42 20/03/2025 9:09
13/05/2024 12:31 20/03/2025 9:09
27/05/2024 6:01 20/03/2025 9:09
27/05/2024 6:03 20/03/2025 9:09
04/06/2024 15:56 20/03/2025 9:09
19/06/2024 13:42 20/03/2025 9:09
26/06/2024 10:37 20/03/2025 9:09
27/06/2024 6:56 20/03/2025 9:09
02/07/2024 10:21 20/03/2025 9:09
13/07/2024 12:28 20/03/2025 9:09
21/07/2024 8:07 20/03/2025 9:09
01/08/2024 7:31 20/03/2025 9:09
05/08/2024 10:07 20/03/2025 9:09
10/08/2024 3:58 20/03/2025 9:09
13/08/2024 8:19 20/03/2025 9:09
15/08/2024 7:48 20/03/2025 9:09
15/08/2024 12:26 20/03/2025 9:09
01/09/2024 14:35 20/03/2025 9:09
22/09/2024 9:00 20/03/2025 9:09
03/10/2024 6:06 20/03/2025 9:09
05/10/2024 6:38 20/03/2025 9:09
07/10/2024 9:46 20/03/2025 9:09
09/10/2024 7:42 20/03/2025 9:09
21/10/2024 13:10 20/03/2025 9:09
26/10/2024 14:17 20/03/2025 9:09
27/10/2024 7:23 20/03/2025 9:09
27/10/2024 7:35 20/03/2025 9:09
28/10/2024 7:48 20/03/2025 9:09
28/10/2024 13:15 20/03/2025 9:09
29/10/2024 6:36 20/03/2025 9:09
31/10/2024 5:28 20/03/2025 9:09
03/11/2024 6:39 20/03/2025 9:09
05/11/2024 8:18 20/03/2025 9:09
05/11/2024 8:21 20/03/2025 9:09
14/11/2024 7:44 20/03/2025 9:09
20/11/2024 7:44 20/03/2025 9:09
28/11/2024 11:07 20/03/2025 9:09
28/11/2024 4:59 20/03/2025 9:09
02/12/2024 6:38 20/03/2025 9:09
02/12/2024 6:40 20/03/2025 9:09
05/12/2024 7:47 20/03/2025 9:09
09/12/2024 11:55 20/03/2025 9:09
12/12/2024 4:51 20/03/2025 9:09
23/12/2024 8:39 20/03/2025 9:09
01/01/2025 6:44 20/03/2025 9:09
02/01/2025 4:54 20/03/2025 9:09
04/01/2025 7:58 20/03/2025 9:09
05/01/2025 6:58 20/03/2025 9:09
05/01/2025 11:12 20/03/2025 9:09
05/01/2025 13:19 20/03/2025 9:09
06/01/2025 7:51 20/03/2025 9:09
06/01/2025 7:58 20/03/2025 9:09
09/01/2025 5:50 20/03/2025 9:09
11/01/2025 8:56 20/03/2025 9:09
11/01/2025 6:05 20/03/2025 9:09
12/01/2025 10:16 20/03/2025 9:09
17/01/2025 5:53 20/03/2025 9:09
17/01/2025 7:03 20/03/2025 9:09
18/01/2025 8:50 20/03/2025 9:09
18/01/2025 13:16 20/03/2025 9:09
20/01/2025 5:33 20/03/2025 9:09
21/01/2025 11:19 20/03/2025 9:09
21/01/2025 12:10 20/03/2025 9:09
22/01/2025 7:57 20/03/2025 9:09
21/01/2025 7:16 20/03/2025 9:09
23/01/2025 10:37 20/03/2025 9:09
23/01/2025 9:17 20/03/2025 9:09
26/01/2025 6:14 20/03/2025 9:09
27/01/2025 7:38 20/03/2025 9:09
27/01/2025 12:08 20/03/2025 9:09
27/01/2025 12:11 20/03/2025 9:09
27/01/2025 12:18 20/03/2025 9:09
27/01/2025 12:19 20/03/2025 9:09
26/01/2025 23:19 20/03/2025 9:09
30/01/2025 8:48 20/03/2025 9:09
30/01/2025 9:03 20/03/2025 9:09
01/02/2025 14:19 20/03/2025 9:09
02/02/2025 6:12 20/03/2025 9:09
02/02/2025 8:50 20/03/2025 9:09
02/02/2025 8:52 20/03/2025 9:09
02/02/2025 8:55 20/03/2025 9:09
01/02/2025 10:27 20/03/2025 9:09
02/02/2025 11:43 20/03/2025 9:09
04/02/2025 19:24 20/03/2025 9:09
04/02/2025 12:53 20/03/2025 9:09
05/02/2025 8:14 20/03/2025 9:09
05/02/2025 8:56 20/03/2025 9:09
05/02/2025 7:16 20/03/2025 9:09
08/02/2025 11:53 20/03/2025 9:09
08/02/2025 11:25 20/03/2025 9:09
08/02/2025 11:32 20/03/2025 9:09
