# Adding time durations as actual numbers

Options
✭✭✭
edited 12/09/19

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?

• Options

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,

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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:

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

"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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 08/16/18
Options

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

I'm going to post the back story on Copy / Paste to my website.

Craig

• ✭✭✭
Options
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
• ✭✭✭✭✭✭
Options

Good luck! Haha.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!