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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hello, Anonymous.

        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