Net Work Duration 11-19-2018 12:49 PM

[Greg_Deckler]

Similar to Net Work Days, Net Work Duration calculates the total duration between two date/time columns taking into account non-working days (weekends) as well as the start and end times for a work day. This version calculates duration in minutes, although this can be easily changed.

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))
// Caclulate the duration of time for the first day
VAR __startDayDuration = DATEDIFF(__startDayTime,__endTime,MINUTE)
// Calculate the end time of the current record
VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
// Calculate the duration of time for the last day
VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime,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)

eyJrIjoiYmNhYzJjY2YtZTBiMC00MDc4LThjNjAtM2YzOGE2M2JkMTdiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[KelvinV]

Hi Greg,

This is just what I need, to ignore any non work hour time, and if possible calculate Saturday halftime as working day.

[Diego09]

do you want it to ignore any non work hour time?

YES

how do you average mNetWorkDuration?

[NVieira]

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_Deckler]

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)

[eduardo_alda]

Hey Greg many thanks for your inputs, could you also consider holidays , based on another "holidays" table?

Thank you so much!

[NVieira]

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?

[Greg_Deckler]

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?