time formula: sum children and convert to hrs and minutes

Lisa Matthews

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

Paul H

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

Lisa Matthews

Hi @Paul H ,

It looks like all the error messages are gone! Thank you! This one worked great!

--Lisa M

Mike TV

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.

Paul H

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

Lisa Matthews

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

Paul H

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")

Lisa Matthews

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:

Paul H

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

Lisa Matthews

Hi @Paul H

This works! Yay! I'm saving this one! Thank you so much for your help! This is awesome!

--Lisa M

Lisa Matthews

https://community.smartsheet.com/discussion/comment/326357#Comment_326357

Hi @Paul H ,

I had one exception come up after applying the last formula. This scenario has only hours and zero minutes--the formula is having trouble with this. Would you please help me to resolve this one?

Thank you,

Lisa M.

Lisa Matthews

Hi @Paul H

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

Paul H

Lets try a different approach

=INT(Hours@row) + "H" + " " + ROUND((Hours@row - INT(Hours@row)) * 60, 2) + "M"

Edited

Lisa Matthews

Hi @Paul H ,

It looks like all the error messages are gone! Thank you! This one worked great!

--Lisa M