Skip to main content

Hi all,



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)



Notes:


-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.



Please help!

Duration fields are stored in seconds, so you’ll need to make sure that your first formula is resulting in seconds, not minutes.

Duration fields are stored in seconds, so you’ll need to make sure that your first formula is resulting in seconds, not minutes.


Thanks @ScottWorld!



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.



For example;


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?



TIA!

Thanks @ScottWorld!



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.



For example;


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?



TIA!


It’s because 30 minutes is 1800 seconds, not 3600 seconds.

It’s because 30 minutes is 1800 seconds, not 3600 seconds.


LOL apologies @ScottWorld this was a typo.



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



I’m not sure how to reconcile the two?

LOL apologies @ScottWorld this was a typo.



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



I’m not sure how to reconcile the two?


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”.

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”.


Ah, so it’s my understanding of decimal to time that needs work here :grinning_face_with_sweat:



Thanks so much @ScottWorld, appreciate the help!

Ah, so it’s my understanding of decimal to time that needs work here :grinning_face_with_sweat:



Thanks so much @ScottWorld, appreciate the help!


LOL, you’re welcome!

Reply


Terms & Conditions