HOWTO: 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 section
  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 Contact Name (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 say 9:00 AM.
  6. Go to cell BB3 and input your work schedule end time 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 the 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 something like the screenshot shown 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 first cell in column "Time to close in Business Hours" (cell V2) and insert the below formula
    =IF(LEN(U2)>0,(NETWORKDAYS(DATEVALUE(N2)+TIMEVALUE(N2),DATEVALUE(U2)+TIMEVALUE(U2),Holidays)-1)*($BB$3-$BB$2)+IF(NETWORKDAYS(DATEVALUE(U2)+TIMEVALUE(U2),DATEVALUE(U2)+TIMEVALUE(U2),Holidays),MEDIAN(MOD(DATEVALUE(U2)+TIMEVALUE(U2),1),$BB$3,$BB$2),$BB$3)-MEDIAN(NETWORKDAYS(DATEVALUE(N2)+TIMEVALUE(N2),DATEVALUE(N2)+TIMEVALUE(N2),Holidays)*MOD(DATEVALUE(N2)+TIMEVALUE(N2),1),$BB$3,$BB$2),"")
  10. Now auto fill/copy & paste the formula to all the rows of the column.
  11. Format the entire "Time to close in Business Hours" (column V) with the custom format as [h]:mm as shown in the screenshot below.
  12. Now, you can see the column V filled with "Time to closure in business hours" for all the closed tickets (tickets with Last Closed At column filled).

Please find attached a sample Excel document which contains two rows with the "Time-to-closure in business hours" filled using the above steps.