Forum Discussion

naomi_j's avatar
naomi_j
Frequent Visitor
6 years ago

Calculate working hours between two dates

I'm trying to calculate the number of working hours/minutes between two dates (ie. excluding non-working hours and weekends).     This is the basic format - and I'm trying to find out how long it t...
Need Help
  • naomi_j's avatar
    naomi_j
    Frequent Visitor
    6 years ago

    Thanks Greg - it seems the Net Work Days option should be close to what I want.  Although I will have the same issue as the other user who commented in the thread (start and finish times potentially occurring out of work hours).  Was there a solution for avoiding the negative numbers?

     

    Thanks again!

    • v-chuncz-msft's avatar
      v-chuncz-msft
      Community Support
      6 years ago

      naomi_j 

       

      You may calculate working hours/minutes since Mon, Feb 24, 2020.

      DIFF ( <Base>, <Resolved> ) - DIFF ( <Base>, <Created> )

       

      • naomi_j's avatar
        naomi_j
        Frequent Visitor
        6 years ago

        Thanks for your response, v-chuncz-msft.  I've not seen this option - are there any articles to explain the function?  


        What would be the <Base> value here?

  • naomi_j's avatar
    naomi_j
    Frequent Visitor
    6 years ago

    Greg_Deckler - I tried your net workdays option and it worked (thankfully no negative numbers output)!  Thanks!  

     

    I changed the output to hours, which works better for me - although this seems to round off rather than offering part hours.  Do you know if there's a way to have the output in part-hours (eg. 1.25 for 1hr12mins instead of 1)?

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago
      naomi_j - Not entirely certain exactly what your code is but perhaps try increasing the number of decimals?
      • naomi_j's avatar
        naomi_j
        Frequent Visitor
        6 years ago
        Greg_Deckler see code below.  I tried to increase the number of decimals, but this doesn't work after the calculation is complete (.00 for all values).  
         
         
        hoursNetworkDuration =
        // Get the start and end dates
        VAR __dateStart = Master_Appended[Created].[Date]
        VAR __dateEnd = Master_Appended[ResolvedNoBlanks].[Date]
        // Calculate the Net Work Days between the start and end dates
        VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
        // Set this to the start of the work day (8:00 AM)
        VAR __startTime = TIME(8,0,0)
        // Set this variable to the end of the work day (5:00 PM)
        VAR __endTime = TIME(17,0,0)
        // Calculate the duration of a full day, in this case in minutes
        VAR __fullDayhours = DATEDIFF(__startTime,__endTime,HOUR)
        // Calculate teh number of full days, this accounts for the possibility that tickets start and end on the same day
        VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
        // Calculate the total duration of all full days.
        VAR __fullDaysDuration = __fullDays * __fullDayhours
        // Calculate the start time of the current record
        VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
        VAR __startDayTime1 = SWITCH(
        TRUE(),
        __startDayTime>__endTime,__endTime,
        __startDayTime<__startTime && __startDayTime>TIME(0,0,0),__startTime,
        __startDayTime
        )
        // Caclulate the duration of time for the first day
        VAR __startDayDuration = DATEDIFF(__startDayTime1,__endTime,HOUR)
        // Calculate the end time of the current record
        VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
        VAR __endDayTime1 = SWITCH(
        TRUE(),
        __endDayTime>__endTime,__endTime,
        __startDayTime<__startTime && __startDayTime>TIME(0,0,0),__startTime,
        __endDayTime
        )
        // Calculate the duration of time for the last day
        VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime1,HOUR)
        // The total duration is the duration of all full days plus the durations of time for the first and last days
        RETURN
        IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,HOUR),__fullDaysDuration + __startDayDuration + __endDayDuration)