How do I add up times?

@TeeM In Smartsheet, numbers entered in this fashion (hh:mm) are stored as text, and therefore math won't work on them without some help.

We can use the VALUE, LEN, LEFT, and RIGHT functions to isolate the hour and minutes values, then do a little conversion to decimal, add them up, and convert back to hh:mm. I recommend having at least one helper column called TimeValue. I'll be calling your column containing the original time values "Times" since I don't know what it's called in your sheet.

Starting with isolating the hour value:

=IF(OR(LEN(Times@row) = 5, LEN(Times@row) = 2), VALUE(LEFT(Times@row, 2)), IF(OR(LEN(Times@row) = 4, LEN(Times@row) = 1), VALUE(LEFT(Times@row, 1)), 0))

English: If the length of Times@row is 5 (ex. 10:27) or 2 (ex. 11), take the leftmost two characters of the cell value and convert them to number value; If the length of Times@row is 4 (ex. 5:13) or 1 (ex. 7), then take the single leftmost character in the cell and convert it to number value; otherwise, set this value to 0.

Next, isolating the minute value

=IF(OR(LEN(Times@row) = 5, LEN(Times@row) = 4), VALUE(RIGHT(Times@row, 2)), 0)

English: If the length of Times@row is 5 (ex. 10:27) or 4 (ex. 5:13), take the rightmost two characters of the cell value and convert them to number value; otherwise, just set this value to 0.

Then we'll want to alter the formula to divide the resulting minute value by 60, in order to convert it to a decimal value:

=IF(OR(LEN(Times@row) = 5, LEN(Times@row) = 4), (VALUE(RIGHT(Times@row, 2)) / 60), 0)

Now we want to add the hour and minute decimal together:

=IF(OR(LEN(Times@row) = 5, LEN(Times@row) = 2), VALUE(LEFT(Times@row, 2)), IF(OR(LEN(Times@row) = 4, LEN(Times@row) = 1), VALUE(LEFT(Times@row, 1)), 0)) + IF(OR(LEN(Times@row) = 5, LEN(Times@row) = 4), (VALUE(RIGHT(Times@row, 2)) / 60), 0)

With this formula, 5:31 would be converted to a numeric value of 5.517.

Taking your list from above, 4:37 becomes 4.617, and the whole hours are now numeric values. Add them all together using =SUM(TimeValue:TimeValue), you get 18.134 hours. If you enforce a decimal place setting on the column, you get 18.13.

Now you take the integer portion of the result, add ":" to it, and add the rounded result of multiplying the decimal portion by .6...

=INT(SUM(TimeValue:TimeValue)) + ":" + RIGHT(ROUND(VALUE(RIGHT(SUM(TimeValue:TimeValue), 3)) * .6, 2), 2)

The result of this is a cell with a text value in hh:mm format of 18:08

Have fun!