How To: Calculate ticket resolution time considering Business Hours (excluding non-working hours, weekends and holidays)

By default, HappyFox Reports compute data taking into account the entire day for calculation of first response time, average response time etc.

To calculate the ticket resolution time, accounting only Business Hours, follow the below steps:

  1. Open the appropriate report under HappyFox --> Reports  --> All Reports.
  2. Scroll down to the bottom of the report, ensure you are in Tabular View and click on the Export Excel option to export the report data as Excel.
  3. Open the Excel document.
  4. Insert a new column between Last Closed At (column U) and Last Closed By (Column V). Once inserted, give the header as "Time to close in Business Hours".
  5. Go to cell BB2 and input your work schedule start time (WST) say 9:00 AM. 
  6. Go to cell BB3 and input your work schedule end time (WET) say 6:00 PM.  
  7. Add a list of holidays with exact dates (in dd-MMM-yyyy format) which should be excluded while calculating time to closure. Once this is done, select all the cells where you have the holiday list and create a range named "HOLIDAYS" without the double quotes. Now the Excel sheet will look like the screenshot below. 

  8. Important Note: When you export the report, the date/time stamps are based on the timezone settings of the staff. This being the case, please ensure the work schedule Start Time and End time are based on the same time zone settings. 

  9. Now go to the first cell in column "Time to close in Business Hours" (cell V2) and insert the below formula
=IF(LEN(U2)>0,(NETWORKDAYS(N2,U2,HOLIDAYS)-1)*($BB$3-$BB$2)+IF(NETWORKDAYS(U2,U2,HOLIDAYS),MEDIAN(MOD(U2,1),$BB$3,$BB$2),$BB$3)-MEDIAN(NETWORKDAYS(N2,N2,HOLIDAYS)*MOD(N2,1),$BB$3,$BB$2),"")
Where U is "Last Closed at" column and N is "Created At".

  10. Now auto fill/copy & paste the formula to all the rows of column. 

  11. Navigate to Format >> Number >> More Formats >> Custom Number Format with the custom format as [h]:mm as shown in the screenshot below.

   12. Now, you can see column V filled with "Time to closure in business hours" for all the closed tickets (tickets with Last Closed At column filled).

  • 171
  • 07-Sep-2019
  • 25813 Views