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
Best Answer

You could use the MOD function to help. Something like:
=ROUND([Primary Column]@row / 60, 0) + ":" + MOD([Primary Column]@row, 60)
Answers

You could use the MOD function to help. Something like:
=ROUND([Primary Column]@row / 60, 0) + ":" + MOD([Primary Column]@row, 60)

Yes. Perfectly simple too!
Thank you!

No problem. Glad it works.

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)

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
Help Article Resources
Categories
Check out the Formula Handbook template!