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 minutes--the 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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!