time formula: sum children and convert to hrs and minutes
Hi,
Can anyone help with a formula to sum time values and convert to hrs and mins? Can't figure out how to write this in SmartSheet.
Thank you in advance for your help.
Lisa
Best Answers

One more try
=LEFT(Hours@row, FIND(".", Hours@row, 1)  1) + "H" + " " + (ROUND((VALUE(RIGHT(Hours@row, LEN(Hours@row)  FIND(".", Hours@row) + 1)) * 60), 2) + "M")
Edited

Hi @Paul H ,
It looks like all the error messages are gone! Thank you! This one worked great!
Lisa M
Answers

The only thing I can think of is writing a formula to say durations between a range is a certain amount of time. You could write a lengthy formula to account for every 15mins. I don't think there's a specific formula written to do exactly what you want with the specific minutes.

Unless one of our resident experts has a better idea, I think you are going to need helper columns
Helper columns hours
Helper Column Minutes
Sum Children Minutes / 60
Sum hours and add the minutes
Finally

Hello @Paul H ,
Thank you for helping me. I added the helper columns and formulas. However, the last formula is not calculating as I expected. The minutes are not appearing correctly in the cell "duration" (sum of all minutes converted to hrs/mins).
This is the result I am seeing:
Thank you very much for taking time to help me with this calculation. It's so close now!
Lisa M

Yes its not liking when the number of decimals changes try this
=LEFT(Hours@row, FIND(".", Hours@row, 1)  1) + "H" + " " + ((VALUE(RIGHT(Minutes@row, FIND(".", Hours@row, 1))) * 60) + "M")

Hi @Paul H
I just tried the new formula. help!
=LEFT(Hours@row, FIND(".", Hours@row, 1)  1) + "H" + " " + ((VALUE(RIGHT(Minutes@row, FIND(".", Hours@row, 1))) * 60) + "M")
These are the results I am seeing:

One more try
=LEFT(Hours@row, FIND(".", Hours@row, 1)  1) + "H" + " " + (ROUND((VALUE(RIGHT(Hours@row, LEN(Hours@row)  FIND(".", Hours@row) + 1)) * 60), 2) + "M")
Edited

Hi @Paul H
This works! Yay! I'm saving this one! Thank you so much for your help! This is awesome!
Lisa M

Hi @Paul H ,
I had one exception come up after applying the last formula. This scenario has only hours and zero minutesthe formula is having trouble with this. Would you please help me to resolve this one?
Thank you,
Lisa M.

Hi @Paul H
Here is another example. In this one, the minutes add up to an even number of hours (meaning, no minutes).

Lets try a different approach
=INT(Hours@row) + "H" + " " + ROUND((Hours@row  INT(Hours@row)) * 60, 2) + "M"
Edited

Hi @Paul H ,
It looks like all the error messages are gone! Thank you! This one worked great!
Lisa M
Help Article Resources
Categories
Check out the Formula Handbook template!