Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, community.
How can I create a calculated column that gives me the working hours between two "date/time" columns, disregarding weekends and holidays, considering business hours from 8am to 6pm?
The below formula worked out just fine on Excel using a HOLIDAYS tab, which later on I could format as "[h]:mm":
=(NETWORKDAYS(B2,C2,HOLIDAYS!$A$2:$A$1000)-1)*("18:00"-"8:00")+IF(NETWORKDAYS(C2,C2,HOLIDAYS!$A$2:$A$1000),MEDIAN(MOD(C2,1),"18:00","8:00"),"18:00")-MEDIAN(NETWORKDAYS(B2,B2,HOLIDAYS!$A$2:$A$1000)*MOD(B2,1),"18:00","8:00")
Unfortunately, I couldn't find a way to do the same on Power BI Desktop.
Could you guys help me, please?
Thank you very much in advance.
Looking forward for your reply.
Best regards
Solved! Go to Solution.
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
Hi @alancoliveira,
You can follow the below steps to get working hours between 2 dates. I just created sample PBIX file, you can refer it with the link.
Best Regards
Rena
Hello, @v-yiruan-msft.
Firstly, thank you very much for your quick response.
I tried to do as you suggested, but considering the example below, as January 12th and 13th are not working days, being the Start Date "11-Jan-2019 11:55" and the End Date "14-Jan-2019 9:30", I'd like for the calculation to count the hours between Jan11 from 11:55 to 18:00, and Jan14 from 08:00 to 09:30, resulting on 7.57 hours (or 7hours and 34 minutes).
Do you know can we achieve that through DAX?
Thanks again.
Looking forward for your reply
Hi @Anonymous ,
Sorry for delay. Please try to create the following calculated column(Assume that both start date and end date are all working day):
1. Calculate the totals work hours between start date and end date(exclude start date and end date)
2. Calculate the working time for start date and end date separately
3. Add the working times for start date and end date into totals work hours
Best Regards
Rena
Hello, @v-yiruan-msft.
I believe we're almost there 😀!
I've created the calculated column according to your last formula, and for most of the cases, it worked out well.
Strangely, on a few cases, the result came out negative or higher than expected, even when the Start and End Dates happened on the same Working Day.
Do you know what might be the issue?
Also, I understand that you're assuming that both Start Date and End Date are all working days, but for me it might happen that a request is raised on weekend or holiday, therefore if somehow the formula can consider that and only make the calculation on working days from 8-18, that would be fantastic.
Thank you very much in advance.
You guys are the best!
Looking forward for your reply.
Best regards
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
@v-yiruan-msft and @Anonymous
Hello,
Can you please share the correct formula? (Or working PBX file will be great)
I am trying to do the same thing and I tried a few other things that I found online. Did not work. Can you please share the formula? I can not download the PBX file. It gives me an error.
Thank you so much
Hello, @v-yiruan-msft.
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, @v-yiruan-msft 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
Unfornately thats not working for me. Is this really the right formula and did it work in your case?
@Anonymous
Thank you so much. I will try this. I appreciate it.
To get networking days , Refer
https://community.powerbi.com/t5/Desktop/NETWORKDAYS-type-function-in-PowerBI/td-p/154902.
On the day it start take diff from end time and date it end take diff from start time. And date diff from Start +1 and end -1.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |