Net Work Duration (Working Hours)
Hi,
I was trying to use this measure, but if we have dates that are outside working hours, the values are not correct ( see attachment).
- Greg_Deckler7 years agoCommunity Champion
So, if the time is outside of the work hours, how would you want the work hours to be calculated, do you want it to ignore any non work hour time?
- Greg_Deckler7 years agoCommunity Champion
If this works for you, I will update the Quick Measure:
mNetWorkDuration = // Get the start and end dates VAR __dateStart = MAX([Date_Start]) VAR __dateEnd = MAX([Date_End]) // 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 (7:30 AM) VAR __startTime = TIME(7,30,0) // Set this variable to the end of the work day (6:00 PM) VAR __endTime = TIME(18,0,0) // Calculate the duration of a full day, in this case in minutes VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE) // 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 * __fullDayMinutes // 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,MINUTE) // 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,MINUTE) // 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,MINUTE),__fullDaysDuration + __startDayDuration + __endDayDuration)- NVieira7 years agoRegular Visitor
Hi Greg,
Thank you very much for your help.
I still get negative values when both start/end time are outside working hours.Date Start - 07-07-2018 21:27:00
Date End - 08-08-2018 00:05:00
I get -445
In this cases i was expecting to have 0 instead of negative values.Beside that, how can i get an average of the mNetWorkDuration?
- eduardo_alda7 years agoFrequent Visitor
Hey Greg many thanks for your inputs, could you also consider holidays , based on another "holidays" table?
Thank you so much!