Forum Discussion
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...
naomi_j - Not entirely certain exactly what your code is but perhaps try increasing the number of decimals?
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)
- I think if you do something like:
IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE) / DATEDIFF(__dateStart,__dateEnd,HOUR) / 60,__fullDaysDuration + __startDayDuration + __endDayDuration)😞 still gives me whole hour output (.00).