Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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 table2. Create one calculated column to judge if the date is working day or Non-working day         3. Create one calculated column to calculate working hours base on the working days between start date and end date         calculate working hours

        

    Best Regards

    Rena

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello, Anonymous.

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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

         

        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