Forum Discussion

BassKroon's avatar
BassKroon
Frequent Visitor
8 years ago

Calculated total time in workingdays/hours

Hello,   I have a problem, im trying to calculate the amount of time a Ticket is open before is has been closed.  I want to know the amount of time a ticket has been open, but not include hours th...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

I believe that this should do it:

 

Column = 
VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR([Date_Start],[Date_End]),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
VAR __startHour = TIME(7,30,0)
VAR __endHour = TIME(18,0,0)
VAR __fullDayMinutes = DATEDIFF(__startHour,__endHour,MINUTE)
VAR __fullDaysDuration = (__NetWorkDays - 2) * __fullDayMinutes 
VAR __startDayHour = TIME(HOUR([Date_Start]),MINUTE([Date_Start]),SECOND([Date_Start]))
VAR __startDayDuration = DATEDIFF(__startDayHour,__endHour,MINUTE)
VAR __endDayHour = TIME(HOUR([Date_End]),MINUTE([Date_End]),SECOND([Date_End]))
VAR __endDayDuration = DATEDIFF(__startHour,__endDayHour,MINUTE)
RETURN  __fullDaysDuration + __startDayDuration + __endDayDuration
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Minor improvement, accounts for tickets that start and end on the same day.

 

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 __startHour = TIME(7,30,0)
// Set this variable to the end of the work day (6:00 PM)
VAR __endHour = TIME(18,0,0)
// Calculate the duration of a full day, in this case in minutes
VAR __fullDayMinutes = DATEDIFF(__startHour,__endHour,MINUTE)
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
VAR __startDayHour = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
VAR __startDayDuration = DATEDIFF(__startDayHour,__endHour,MINUTE)
VAR __endDayHour = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
VAR __endDayDuration = DATEDIFF(__startHour,__endDayHour,MINUTE)
RETURN  __fullDaysDuration + __startDayDuration + __endDayDuration