Help

Time Formula/Calculation for Expected Output in Duration

Topic Labels: Dates & Timezones
152 7
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

7 Replies 7

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!

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?

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!