I am looking to calculate average length of phone calls
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.
Answers
-
L_123 ✭✭✭✭✭✭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.
-
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[email protected]with your reference
=VALUE(LEFT([email protected], FIND(".",[email protected]) - 1)) + (VALUE(RIGHT([email protected], LEN([email protected]) - FIND(".",[email protected]))) - MOD(VALUE(RIGHT([email protected], LEN([email protected]) - FIND(".",[email protected]))), 60)) / 60 + ":" + MOD(VALUE(RIGHT([email protected], LEN([email protected]) - FIND(".",[email protected]))), 60)
Help Article Resources
Categories
=DATEONLY(Created@row) + IF(AND(FIND(\"P\", Created@row)> 0, VALUE(MID(Created@row, FIND(\" \", Created@row) + 1, FIND(\":\", Created@row) - (FIND(\" \", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(\" \", Created@row) + 1, FIND(\":\", Created@row) - (FIND(\" \", Created@row) + 1))) < 12), 1, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":227,"urlcode":"api-and-developers","name":"API and Developers"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":277,"urlcode":"Integrations","name":"Integrations"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":369,"urlcode":"bridge-by-smartsheet","name":"Bridge"}]},{"discussionID":108222,"type":"question","name":"Parent Status based on Children Statuses - Need help refining this formula","excerpt":"Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision. Here's the formula: =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), \"Complete\"), \"Complete\", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(),…","snippet":"Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders…","categoryID":322,"dateInserted":"2023-07-27T20:58:59+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T12:20:53+00:00","insertUserID":146258,"insertUser":{"userID":146258,"name":"dhall","url":"https:\/\/community.smartsheet.com\/profile\/dhall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T12:21:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":146258,"lastUser":{"userID":146258,"name":"dhall","url":"https:\/\/community.smartsheet.com\/profile\/dhall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T12:21:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3381040192,"url":"https:\/\/community.smartsheet.com\/discussion\/108222\/parent-status-based-on-children-statuses-need-help-refining-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108222\/parent-status-based-on-children-statuses-need-help-refining-this-formula","format":"Rich","tagIDs":[219,254],"lastPost":{"discussionID":108222,"commentID":387762,"name":"Re: Parent Status based on Children Statuses - Need help refining this formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387762#Comment_387762","dateInserted":"2023-07-28T12:20:53+00:00","insertUserID":146258,"insertUser":{"userID":146258,"name":"dhall","url":"https:\/\/community.smartsheet.com\/profile\/dhall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T12:21:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/P3FFJGV2II8T\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T12:21:14+00:00","dateAnswered":"2023-07-28T00:26:52+00:00","acceptedAnswers":[{"commentID":387717,"body":"
Give this a try:<\/p>
=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Canceled\"), \"Canceled\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = \"Complete\", @cell = \"Canceled\", @cell = \"On Hold\")), \"Complete\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Not Started\"), \"Not Started\", IF(COUNTIFS(CHLDREN(), @cell = \"Upcoming\")> 0, \"Upcoming\", \"In Progress\"))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108218,"type":"question","name":"Help with \"AND\" formula","excerpt":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an \"Invalid Data type\". I've confirmed all references are correct. Anyone have ideas? =AVERAGEIF(AND({Complexity Year}, \"2019\", {Product},…","snippet":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm…","categoryID":322,"dateInserted":"2023-07-27T20:23:56+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":156865,"lastUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3381039668,"url":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108218,"commentID":387766,"name":"Re: Help with \"AND\" formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387766#Comment_387766","dateInserted":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T12:46:42+00:00","dateAnswered":"2023-07-27T22:53:31+00:00","acceptedAnswers":[{"commentID":387693,"body":"
AVERAGEIF and AND do not play well together. Try this one, it collects all values that meet your criteria and then averages them. I am assuming {Complexity Score New} is the range with the values you would like to average.<\/p>
=AVG(COLLECT({Complexity Score New}, {Complexity Year}, \"2019\", {Product}, \"ESP\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
