Forum Discussion

tex628's avatar
tex628
Community Champion
8 years ago
Solved

Calculating working hours

Hello,

 

I have a table that looks like this: 


 

I need to calculate the hours between the created on/at and the finalized on/at. 
We count all hours that occur, 24/7 except for weekends and significant holidays. 

I've tried alot but i cant find a solution to disregard the weekends and holidays...

 

Anyone have any advice on how to tackle this? 

 

/ Johannes

Need Help
  • kohlivinayak's avatar
    kohlivinayak
    8 years ago

    Hi tex628

     

    I tried recreating your scenario and calculating no of days excluding weekend days.

     

    So I created a date dimention and with weeknum function got saturdays (6) and sundays(7) in a new column in date dimention.

     

     

     

     

     

     

     

     

     

     

     

     

    And in my start and end date table first calculated datediff in no of days

    and then no of weekend days in between these two days by creating new column using formula as

     

    Weekend Days =
    var _stdate = Working[Date 1]
    var _enddate = Working[Date 2]
    return
    CALCULATE(COUNT('Date Dim'[Date]),FILTER('Date Dim','Date Dim'[Day Name] =6 || 'Date Dim'[Day Name] = 7),FILTER('Date Dim','Date Dim'[Date]>= _stdate && 'Date Dim'[Date] <= _enddate))

     

    And lastly subtracting both

     

     

     

     

     

     

     

     

     

     

    Thanks

    Vinayak Kohli

4 Replies

  • kohlivinayak's avatar
    kohlivinayak
    Resolver I
    8 years ago

    Hi tex628

     

    I tried recreating your scenario and calculating no of days excluding weekend days.

     

    So I created a date dimention and with weeknum function got saturdays (6) and sundays(7) in a new column in date dimention.

     

     

     

     

     

     

     

     

     

     

     

     

    And in my start and end date table first calculated datediff in no of days

    and then no of weekend days in between these two days by creating new column using formula as

     

    Weekend Days =
    var _stdate = Working[Date 1]
    var _enddate = Working[Date 2]
    return
    CALCULATE(COUNT('Date Dim'[Date]),FILTER('Date Dim','Date Dim'[Day Name] =6 || 'Date Dim'[Day Name] = 7),FILTER('Date Dim','Date Dim'[Date]>= _stdate && 'Date Dim'[Date] <= _enddate))

     

    And lastly subtracting both

     

     

     

     

     

     

     

     

     

     

    Thanks

    Vinayak Kohli

    • tex628's avatar
      tex628
      Community Champion
      8 years ago

      Thanks for your response!

       

      I'm looking for an exact value in hours and not days. This is the baseline for case-handling statistics, the amount of time is often lower than a day.

       

      Br,

      Johannes

      • kohlivinayak's avatar
        kohlivinayak
        Resolver I
        8 years ago

        Hi tex628

         

        Take the difference in hours and multiply the number of weekend days with 24 and subtract them.

         

        for finding time difference you need to concat date and time together.