Summing up time in the format HOURS:MINS:SECS

Jordan Norris

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!

Jordan Norris

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?

Paul Newcome

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

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time