Time Formula/Calculation for Expected Output in Duration

[Lauren_Prete]

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!

[ScottWorld]

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

[Lauren_Prete]

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!

[ScottWorld]

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

[Lauren_Prete]

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?

[ScottWorld]

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

[Lauren_Prete]

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!

[ScottWorld]

LOL, you’re welcome!