I am looking to calculate average length of phone calls

Benjamin Strauss

I am looking to calculate average length of phone calls we do. The team puts in time of call and I want to average it. I converted the times into decimals so I can sum them, I am having trouble converting the totals back into mm:ss format. Please advise.

L_123

https://community.smartsheet.com/discussion/comment/265084#Comment_265084

https://community.smartsheet.com/discussion/comment/265084

The thread above might help you. If you are still having issues then I would be happy to help you troubleshoot a formula if you show what you already have.

Benjamin Strauss

Screenshot 2020-10-15 085447.png

So I have the calculations I need, but how do I turn this back to mm:ss format. If it is just seconds it is an easy multiple by 60, but once I have a minutes I am lost. I need to create one formula that will handle this task. any ideas?

L_123

You could try using text parsing. Something like below if you replace c@row with your reference

=VALUE(LEFT(c@row, FIND(".", c@row) - 1)) + (VALUE(RIGHT(c@row, LEN(c@row) - FIND(".", c@row))) - MOD(VALUE(RIGHT(c@row, LEN(c@row) - FIND(".", c@row))), 60)) / 60 + ":" + MOD(VALUE(RIGHT(c@row, LEN(c@row) - FIND(".", c@row))), 60)