Summing up time in the format HOURS:MINS:SECS
Hi All,
I have a list of tasks with estimated time of duration in the format Hours : Mins : Secs
I am looking to sum up the total of all tasks however I do not think Smartsheet likes to sum up values that have a semi colon breaking up the number. Is there a way around adding these up?
Thanks!
Answers
-
Currently I have 2 formulas in separate columns, This works when the time is presented like Hours : Mins. But not when the seconds are added on.
For Splitting out the hours:
=VALUE(LEFT([Planned Mhrs]@row, FIND(":", [Planned Mhrs]@row) - 1))
For splitting out the mins:
=VALUE(RIGHT([Planned Mhrs]@row, LEN([Planned Mhrs]@row) - FIND(":", [Planned Mhrs]@row)))
Of course we then need to sum each column and convert the minutes columns total back to hours and minutes and then add this to the sum of hours column for a full total.
Is there an easier way to do what I am trying to achieve as this feels very long winded?
-
There is unfortunately no straightforward way (yet) to do time calculations in Smartsheet.
Here is a link to a thread that has a lot of time based solutions in it. As of this post there are 10 pages, so you may need to dig a little, but there should be something in there for you...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 68K Get Help
- 474 Global Discussions
- 209 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 85 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!