Minutes to Hours:Minutes

Chris Shields

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

David Tutwiler

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

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

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