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

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)
    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago
      I think if you do something like:

      IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE) / DATEDIFF(__dateStart,__dateEnd,HOUR) / 60,__fullDaysDuration + __startDayDuration + __endDayDuration)
      • naomi_j's avatar
        naomi_j
        Frequent Visitor
        6 years ago

        😞 still gives me whole hour output (.00).