Minutes to Hours:Minutes

Hello,

Most of our tasks are factored by the minute, for manufacturing purposes. Let's say a task takes a minute to run, but I'm doing it 125 times. Simple math = 125 minutes. What I want to show is a cell is how many hours and minutes that is for easier human readability on the reports. So that I know that it'll take 2 hours and 6 minutes, or 2:06.

How would I accomplish this in Smartsheets?

I know how I'd do it on paper:

125/60 = 2.086333

Focus on the decimal:

.086333 * 60 = 5.17998

Round up to 6

Add the hour back on and format:

2:06

I could do this in java but for the life of me can't figure out how to do it on Smartsheets

Find more posts tagged with

Accepted answers

David Tutwiler

You could use the MOD function to help. Something like:

=ROUND([Primary Column]@row / 60, 0) + ":" + MOD([Primary Column]@row, 60)

All comments

David Tutwiler

You could use the MOD function to help. Something like:

=ROUND([Primary Column]@row / 60, 0) + ":" + MOD([Primary Column]@row, 60)

Chris Shields

Yes. Perfectly simple too!

Thank you!

David Tutwiler

No problem. Glad it works.

Gancsos

I suggest using ROUNDDOWN instead of ROUND.

If you use ROUND in the formula, then you will get inaccurate results. The formula about will take "45" and return a result of "1:45", because 45/60 = 0.75, which gets rounded up to 1. If you use ROUNDDOWN, you will get 0 for the HH component, which will show as "0:45", meaning "zero hours, 45 minutes".

Just use this instead:

=ROUNDDOWN([Primary Column]@row / 60, 0) + ":" + MOD([Primary Column]@row, 60)

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

David Noël

Is there a way to allow any minutes less than 10 to show up with leading zero?

For example, 2 hours and 7 minutes would show up at 2:07 and not 2:7

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.