Adding time durations as actual numbers
![dchouston](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hello,
We're trying to use Smartsheet to report on music details. It would be very useful to take the durations of music tracks (formatted either as mm:ss or h:mm:ss) and add them together together for a total duration. How would we attempt this?
Comments
-
If you locate successive tracks as children tasks connected to each other by a FS link (Finish-Start, i.e. the standard link between tasks)
Then position a parent task on top of these → the parent will sum up the all sequence automatically.
Alternatively, you can use "+" or "=SUM(...)"
(Sorry I might have missed something because my answer looks too obvious. Do not hesitate to address more constraints if any)
Respectfully yours,
-
Unfortunately Smartsheet is not capable of calculating time as far as hours/minutes/seconds go. There are a few workarounds out there in some other threads, but they are all rather tedious and complex.
-
Since your data is formatted like this
[HHH]H:MM:SS
(assuming hours could be more than 9)
this formula will give you the seconds of a single cell:
=VALUE(LEFT(TL@row, FIND(":", TL@row) - 1)) * 3600 + VALUE(MID(TL@row, FIND(":", TL@row) + 1, 2)) * 60 + VALUE(RIGHT(TL@row, 2))
(My column name is TL)
I would add a column to house this and then sum that column.
Craig
-
"I would add a column to house this"
We can call this column "Seconds".
"and then sum that column."
If you put the following formula into a column other than the Seconds column (top row of the TL column in Craig's example is what I suggest), Then it will sum all of the rows in the Seconds column and convert it back to HH:MM:SS.
=INT(SUM(Seconds:Seconds) / 3600) + ":" + IF(INT((SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600)) * 3600)) / 60) < 10, "0" + INT((SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600)) * 3600)) / 60), INT((SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600)) * 3600)) / 60)) + ":" + IF((SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600) * 3600) + VALUE(INT((SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600)) * 3600)) / 60) * 60))) < 10, "0" + (SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600) * 3600) + VALUE(INT((SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600)) * 3600)) / 60) * 60))), (SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600) * 3600) + VALUE(INT((SUM(Seconds:Seconds) - (VALUE(INT(SUM(Seconds:Seconds) / 3600)) * 3600)) / 60) * 60))))
If you're looking to use parent rows to show a total of each child row, you would replace Seconds:Seconds with CHILDREN(Seconds@row).
-
Here's a shorter version:
=INT(Sec@row / 3600) + ":" + IF(INT((Sec@row - INT(Sec@row / 3600) * 3600)) < 600, "0") + INT((Sec@row - INT(Sec@row / 3600) * 3600) / 60) + ":" + IF(Sec@row - (INT(Sec@row / 3600) * 3600) - (INT((Sec@row - INT(Sec@row / 3600) * 3600) / 60) * 60) < 10, "0") + (Sec@row - (INT(Sec@row / 3600) * 3600) - (INT((Sec@row - INT(Sec@row / 3600) * 3600) / 60) * 60))
I assume the Sec@row is the summed value.
Why this is shorter:
Instead of doing this:
IF (a very long formula is less than 10, "0" + a very long formula, a very long formula)
I do this:
IF (a very long formula is less than 10, "0") + a very long formula
Craig
-
I assume the Sec@row is the summed value.
In my formula, I used the column you put YOUR formula in and called it "Seconds" since it would convert HH:MM:SS into seconds by row.
With your single conversion column going by row, there wasn't a SUM yet. That's why I used Seconds:Seconds. To sum up all of the cells in your conversion column AND convert it back to HH:MM:SS all at the same time.
I do this:
IF (a very long formula is less than 10, "0") + a very long formula
That's definitely a good idea. It saves on space, and depending on the length of the overall formula, is less likely to exceed the 4,000 characters/cell limit.
I would just copy/paste the "very long formula" to avoid typos. Since it isn't very many keystrokes doing it that way, I didn't even think about doing it your way. Definitely going to try to keep it in mind though.
-
"Copy / Paste is the work of the Devil" - Craig Williams
Theoretically
A = SUM(a bunch of stuff)
B = use A a bunch of times
is faster than
B = SUM(a bunch of stuff) a bunch of times.
My personal character limit on formulas is something less than 500. If it is longer than 100, I reevaluate to see what I'm missing.
Craig
-
I love copy/paste when I know something is working. I have fat fingers.
And I see what you're doing. You're going ahead and summing up all of the seconds SUM(Seconds:Seconds) and THEN converting it back to HH:MM:SS.
Even breaking it down into 2 separate steps, it's still shorter and only one more extra cell used. This is why I typically defer to you, the Smartsheet Yoda. HAHA!
dchouston: Craig has the best solution (so far). Only look at my posts if you want to learn from my mistakes. Otherwise... You can ignore them.
-
Your answers aren't mistakes. They get the job done. In this case, one is it working, it is unlikely it will be changed - does anyone need to see DD:HH:MM:SS?
But ...
"Simple is better than complex. Complex is better than complicated." - Tim Peters
Glad I am willing to listen to my advice you are.
I'm going to post the back story on Copy / Paste to my website.
Craig
-
Thanks all...Smartsheet really makes you work for it, don't they!
I've got it all working. Now let's see if I can explain it to radio hosts...
Cheers,
David -
Good luck! Haha.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!