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 that are between 07:29AM - 06:01PM

 

So a column that gives me the hours worked on a ticket, friday 05:55PM Opened en Closed at monday 07:35AM will give me a 10 back in minutes. 

 

Lets say a day starts at 07:30AM and ends at 06:00PM, and i want to exclude the weekends. 

 

I have made a small sample of the data, so i hope you'll understand it better:

(in this sample, the start date is on a friday en the end date is the monday after the weekend)

 

 

 

 

Thanks, Bas

Need Help

3 Replies

    • 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