HH:MM sum formula in Smartsheet

Tamara Diver

I have a series of times that our staff spend on a particular activity for a month in format HH:MM as shown below but I'm struggling with how to create a formula in smartsheet that allows me to sum these values are report the result in the format HH:MM too? Can anyone assist?

Paul Newcome

HERE is a thread that contains multiple time based solutions. Take a look through them and see if you are able to find one that will work for you. If you are unable to find something or need help adapting one to fit your needs, feel free to let me know, and I will be happy to help.

Stefan

Hi @Tamara Diver ,

how about using a column for the hours and a column for the minutes? As you are requesting 15min increments you could use dropdown there. I think it would probably even faster to fill in for your staff.

The formula to get the total looks like this:

=SUM(hours2:hours6) + FLOOR(SUM(minutes2:minutes6) / 60, 1) + ":" + (SUM(minutes2:minutes6) - FLOOR(SUM(minutes2:minutes6) / 60, 1) * 60)

hours2:hours6 = the range of hour cells to be summed

minutes2:minutes6 = = the range of minute cells to be summed

The formula should work up till 99:45 ;-)

For FLOOR and SUM have a look here:

https://help.smartsheet.com/function/floor

https://help.smartsheet.com/function/sum

Hope this helps

Stefan