2

Response time KPI – Currently, the response time is including the hours post 6pm (out of working hours). Need to update the calculations such that response time is based on the working hours Monday to Sunday, 9am to 5pm. For example, if an enquiry comes in after 5pm, the response time should only be calculated from the next day 9am onwards.

id = id[Response Time == null];
if (time(id.get("Received Time")) >= time("09:00:00") and time(id.get("Received Time")) <= time("17:00:00"))
{
  id.put("Response Time", id.get("Received Time"));
}
else
{
  next_day = dateAddDays(date(id.get("Received Time")), 1);
  response_time = dateAddHours(date(next_day), 9);
  id.put("Response Time", response_time + time("00:00:00"));
}
ZohoCoder
  • 385
  • 5
  • 15
Quinn C
  • 31
  • 4

1 Answers1

0

Based on your requirements, I see that you want to calculate response time start from 9 AM to received time even though the inquiry time is out of the scope of 9 AM to 5 PM of the day. I assumed your response time is the total hours consumed from inquiry time to received time.

Your requirements should have these results;

  1. inquiry time = 11-Apr-2023 09:30:55 , received time = 12-Apr-2023 10:40:55 , and the response time should be 1.53 hours.

  2. inquiry time = 12-Apr-2023 09:30:55 , received time = 12-Apr-2023 10:40:55 , and the response time should be 1.17 hours.

The script for that is;

//Use 24-hour clock system
enquiryTime = '11-Apr-2023 09:30:55';
recievedTime = '12-Apr-2023 10:40:55';

isEnquiryTimeRecievedOnWorkingHours = false;

workStartTime =(recievedTime.toDate() +  " 9:00:00").toDateTime(); //9 AM
workEndTime = (recievedTime.toDate() +  " 17:00:00").toDateTime(); //5 PM

responseTimeTotalHrs = 0;

enquiryHrs =  enquiryTime.getHour();
enquiryMins = enquiryTime.getMinutes();
enquirySecs = enquiryTime.getSeconds();


recievedHrs =  recievedTime.getHour();
recievedMins = recievedTime.getMinutes();
recievedSecs = recievedTime.getSeconds();

workStartHrs =  workStartTime.getHour();
workStartMins = workStartTime.getMinutes();
workStartSecs = workStartTime.getSeconds();

if (enquiryTime >= workStartTime && enquiryTime <= workEndTime){
    isEnquiryTimeRecievedOnWorkingHours = true;
}
if (isEnquiryTimeRecievedOnWorkingHours == true){
    responseTimeTotalHrs =  (recievedHrs - enquiryHrs) +(recievedMins/60 - enquiryMins/60) + (recievedSecs/3600 - enquirySecs/3600) ;
}
else{
    responseTimeTotalHrs = (recievedHrs - workStartHrs) +(recievedMins/60 - workStartHrs/60) + (recievedSecs/3600 - workStartHrs/3600) ;
}


info responseTimeTotalHrs.round(2);

The info result is : 1.53 hours.

You can test this deluge script at https://dre.zoho.com/tryout. Feel free to comment if it is not what you wanted.

von-tastic
  • 81
  • 5