Got a real brain teaser here (or at least one my brain can’t handle lol).
I’m trying to calculate the ACTUAL DURATION - EXPECTED DURATION of ticket processing time for our team.
The EXPECTED DURATION field is calculated:
NO OF TICKETS(integer)*5.5(mins)/2(agents)+30(mins)
-5.5mins is our average handle time for 1 ticket
-we have 2 agents on our team
-each agent receives a 30min break per day
This formula currently returns a decimal value (I have tried to set this as a duration but it’s giving incorrect results).
The ACTUAL DURATION field is calculated:
TIME START - TIME FINISH
This formula returns a duration value
I am wondering if there is a way to produce this master formula to let me know when our team goes over expected time to resolve tickets and vise-versa so I don’t physically have to do the calculation every day.
If I adjust my formula for EXPECTED DURATION to:
NO OF TICKETS(integer)*330(sec)/2(agents)+3600(sec) and set the formatting to duration, this produces results with information I’m not sure is accurate.
We have 27 tickets for the day.
1st formula: NO OF TICKETS(integer)*5.5(mins)/2(agents)+30(mins) = 104.25mins in decimal, 1.74hrs in decimal
2nd formula: NO OF TICKETS(integer)*330(sec)/2(agents)+3600(sec) = 1:44hrs in duration
Are you able to see where/if I’m going wrong here?
My 2nd formula does calculate as below and return the below result which remains inconsistent with the result retrieved in the 1st formula when calculating in minutes/decimals:
NO OF TICKETS(integer)*330(sec)/2(agents)+1800(sec) = 1:44hrs in duration
That’s the right answer, because duration is showing you hours & minutes. 1.74 hours is the equivalent of 1 hour and 44 minutes. If you want to see 1.74, then forget converting to seconds, and change the formatting of the field to “decimal” instead of “duration”.