How do I calculate the average time a representative takes to resolve a call per month?

cibfit

I have created a weekly intake form that managers complete documenting how many calls their employee handled as well as the average length they were on the calls. I am creating a roll-up report where I need to calculate the monthly average length of time it took each employee to handle the call.

This screenshot show how the data comes into the sheet

This is the summary sheet

This is the formula I am using

=AVG(COLLECT({Avg ACD Time- Tier 1 Range 4}, {Employee Name - Tier 1 Range 2}, [Employee Name]@row, {Week Ending Date Handling - Tier 1 Range 5}, >=DATE(2022, 8, 1), {Week Ending Date Handling - Tier 1 Range 5}, <=DATE(2022, 8, 31)))

EvermoreCoffee

Working with time in Smartsheet can be tricky as there is no formulas that handle time directly. One way that we've got around this is to convert the time value into an integer within a helper column, representing the minute of the day (so 14:21 would be 861).

Here is a snippet to get value to the left of the colon:

=VALUE(LEFT([TimeColumn]@row, FIND(":", [TimeColumn]@row) - 1)) * 60

And the remaining minutes to the right of the colon:

=VALUE(RIGHT([TimeColumn]@row, FIND(":", [TimeColumn]@row) - 1))

You may need to tweak this to represent the data you need, though should give a foundation for getting the AVG() as a number, then once you have the average, convert it back into your mm:ss for presentation.

I know this doesn't quite answer the question, though hope it helps in one form or another!

Nick Korna

The error is caused because the times are not numbers (due to the : for the divider).

You can get round this by having a helper column which will convert your value into a decimal so the AVG will work:

=VALUE(LEFT([Avg. ACD Time]@row, (FIND(":", [Avg. ACD Time]@row) - 1))) + (((VALUE(RIGHT([Avg. ACD Time]@row, 2)) / 60)))

Obviously this will then cause you some headaches because you will end up with your average as a decimal rather than a time in (MM:SS) format in your summary sheet, but you can then use another formula to fix this:

=LEFT(Decimal@row, FIND(".", Decimal@row) - 1) + ":" + IF(ROUNDUP(VALUE(MID(Decimal@row, (FIND(".", Decimal@row)), 6)) * 60, 0) > 10, ROUNDUP(VALUE(MID(Decimal@row, (FIND(".", Decimal@row)), 6)) * 60, 0), ("0" + ROUNDUP(VALUE(MID(Decimal@row, (FIND(".", Decimal@row)), 6)) * 60, 0)))

Data shown below so you can rearrange as desired:

I think the rest of your formula is fine, just needs to sub in the decimal values for the normal time format.

Help this is of at least some assistance!

Keenan Jenkins

Is there a way we can use the formula above without having to round the time up to the nearest second?

Genevieve P.

Hey @Keenan Jenkins

Can you clarify your use-case and what you need to do? There's a new TIME function you may want to try out:

https://help.smartsheet.com/function/time