Formula for converting text/numbers - not working

I am trying to convert a total from "total time" column into something that shows "time" (so to speak). In this example the "total time" is 135 (minutes). So in Total Time (hh:mm) I need it to read , at the very least, 2.15 (doesn't have to be in hh:mm format).

When I run my own formula or auto generate a formula it only gives me a sum of 2 (even after instructing it to round to 2 decimals, etc).

Help, please I am ready to throw my keyboard 🤣

Find more posts tagged with

Accepted answers

All comments

Mark D

Hello @jacquedale,

Time formulas have always been my nemesis. I played around with this and I believe I may have a solution for you. But first, you are getting "2" in your output because 2.15 as a decimal doesn’t represent 2 hours, 15 minutes: 2.15 hours ≈ 2 hours, 9 minutes (because 0.15 × 60 = 9 minutes).

[Total Time]@row / 60 gets you 2.25 but that represents 2 1/4.

So breaking it down to just get hours first I used:

=INT([TotalTime]@row / 60) = 2

Then to get the minutes remaining I used:

135 - (60 × 2) = 15

Then concatenating the INT(135/60) with the output gave me 2:15

=INT([Total Time]2 / 60) + ":" + RIGHT("0" + ([Total Time]2 - 60 * INT([Total Time]2 / 60)), 2)

Try it out and let me know, thanks.

Paul Newcome

I would suggest this:

=INT([Total Time]@row / 60) + ":" + RIGHT("0" + ([Total Time]@row - (INT([Total Time]@row / 60) * 60)), 2)

You may also find other useful time based calculations in this thread (it is 19 pages long so may take some digging, but there are a lot of solutions tucked away in there):

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.