Forum Discussion
Calculate working hours between two dates
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!
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!
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