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

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

Anonymous's avatar
Anonymous
Not applicable
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

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • bikelley's avatar
        bikelley
        Helper IV
        5 years ago

        Anonymous 

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

  • bikelley's avatar
    bikelley
    Helper IV
    5 years ago

    Anonymous 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