Forum Discussion
Calculating working hours between 2 dates disregarding weekends and holidays
Hi Anonymous ，
I rewrite the formula for calculating the working time between 2 dates, you can get the formula from PBIX file shared in OneDrive.
Best Regards
Rena
Hello, Anonymous.
You're amazing!
It worked out great!
I hope this solution could also be useful to someone else with the same requirement.
Thank you very much, Anonymous and the Power BI Community.
Have a great day!
Best regards
Hello, bikelley
You can find below the DAX formula for the calculated column "Working time".
Just pointing out that it was necessary to have a Calendar Table that showed if the date was a working day or not, and also the working time between 08:00 and 18:00 was manually specified on the formula.
Working time =
VAR evaSdate =
CALCULATE (
COUNTROWS ( 'Calendar table' ),
FILTER ( 'Calendar table', 'Calendar table'[Isworkday] = 1 ),
DATESBETWEEN (
'Calendar table'[Date],
'Work Date'[Start date],
'Work Date'[Start date]
)
)
VAR evaEdate =
CALCULATE (
COUNTROWS ( 'Calendar table' ),
FILTER ( 'Calendar table', 'Calendar table'[Isworkday] = 1 ),
DATESBETWEEN (
'Calendar table'[Date],
'Work Date'[End date],
'Work Date'[End date]
)
)
VAR worktime =
CALCULATE (
COUNTROWS ( 'Calendar table' ) * ( 18 - 8 ),
FILTER ( 'Calendar table', 'Calendar table'[Isworkday] = 1 ),
DATESBETWEEN (
'Calendar table'[Date],
'Work Date'[Start date],
'Work Date'[End date]
)
)
- IF (
evaSdate = 1,
IF (
HOUR ( 'Work Date'[Start date] ) < 8,
0,
IF (
HOUR ( 'Work Date'[Start date] ) >= 8
&& HOUR ( 'Work Date'[Start date] ) <= 18,
(
HOUR ( 'Work Date'[Start date] )
+ MINUTE ( 'Work Date'[Start date] ) / 60
) - 8,
10
)
),
0
)
- IF (
evaEdate = 1,
IF (
HOUR ( 'Work Date'[End date] ) < 8,
10,
IF (
HOUR ( 'Work Date'[End date] ) >= 8
&& HOUR ( 'Work Date'[End date] ) <= 18,
(
18
- (
HOUR ( 'Work Date'[End date] )
+ MINUTE ( 'Work Date'[End date] ) / 60
)
),
0
)
),
0
)
RETURN
worktime
I hope it's usefull to your requirement.
Best regards
Anonymous
Thank you so much. I will try this. I appreciate it.
Unfornately thats not working for me. Is this really the right formula and did it work in your case?
