cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alancoliveira
Frequent Visitor

Calculating working hours between 2 dates disregarding weekends and holidays

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

1 ACCEPTED SOLUTION

Hi @alancoliveira

I rewrite the formula for calculating the working time between 2 dates, you can get the formula from PBIX file shared in OneDrive

getworkingtime.JPG

 

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
yingyinr
Community Support
Community Support

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.

  1. Create one calendar table with date and holiday information        calendar tablecalendar table2. Create one calculated column to judge if the date is working day or Non-working day         evaluate isworkingday.png3. Create one calculated column to calculate working hours base on the working days between start date and end date         calculate working hourscalculate working hours

    

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, @yingyinr.

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).

 

working_hours_test.jpg

 

 

Do you know can we achieve that through DAX?

Thanks again.
Looking forward for your reply

 

 

Hi @alancoliveira ,

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

calworkingtime.JPG

 

Worktime =
CALCULATE (
    (COUNTROWS ( 'Calendar table' ) -2) * ( 18 - 8 ),
    FILTER ( 'Calendar table', 'Calendar table'[Isworkday] = 1 ),
    DATESBETWEEN (
        'Calendar table'[Date],
        'WorkDates'[Start date],
        'WorkDates'[End date]
    )
) + IF (
            HOUR ( 'WorkDates'[End date]) < 8,
            0,
            IF (
                HOUR ( 'WorkDates'[End date] ) > 8
                    && HOUR ( 'WorkDates'[End date] ) < 18,
                (
                    HOUR ( 'WorkDates'[End date] )
                        + MINUTE ( 'WorkDates'[End date] ) / 60
                ) - 8,
                10
            )
        )
        + IF (
            HOUR ( 'WorkDates'[Start date] ) < 8,
            10,
            IF (
                HOUR ( 'WorkDates'[Start date] ) > 8
                    && HOUR ( 'WorkDates'[Start date] ) < 18,
                18
                    - (
                        HOUR ( 'WorkDates'[Start date] )
                            + MINUTE ( 'WorkDates'[Start date] ) / 60
                    ),
                0
            )
        )

 

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, @yingyinr.

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?

 

Working_hours_issue.jpg

 

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 @alancoliveira

I rewrite the formula for calculating the working time between 2 dates, you can get the formula from PBIX file shared in OneDrive

getworkingtime.JPG

 

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@yingyinr and @alancoliveira 

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, @yingyinr.
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, @yingyinr 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?

@alancoliveira 

Thank you so much. I will try this. I appreciate it. 

amitchandak
Super User
Super User

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.

 

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors