How do I add up times?

TeeM

If a user is entering times as hh:mm, how do I add these up?

e.g.

3

4:37

5

5:31

Jeff Reisman

@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!

TeeM

Thanks @Jeff Reisman ,

Sounds complicated but I will give it a go! 😊

TeeM

Hello @Jeff Reisman

Could I please share a sheet with you so that you can demonstrate?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/364413#Comment_364413

Here is the formula in action with your data values:

After plugging this all into a test sheet I realized I needed a tweak to the decimal portion of the last part:

For this change to the decimal part: I changed it to use FIND to locate the position of the decimal point, and used that position in the MID formula to say "start at the decimal and collect the next 3 characters, which returned a text value of .13. Then I converted that back to number value and multiplied by .6 to convert it back to minutes, and used ROUND to round it to 2 decimal places, 0.08. Then I used MID again to just collect the "08".

TeeM

@Jeff Reisman

Thanks Jeff, I will try that.