Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

How to sum durations of time?

Robin Doyle
edited 12/09/19 in Archived 2016 Posts

I have multiple columns that are formatted as durations in hours. I need a formula that can add these together, and show the results in hours too. In Excel, you can specify the formatting using [h] but it's looking like Smartsheet can only add numbers and dates, not durations of time. Any advice greatly appreciated!


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    Are you summing across rows or columns?

    Do you have text like "1h" as your duration?

    The only true "Duration" field is the one assigned to that functionality in the Project Setttings. All the rest are just text/numbers, even if you name them "Duration 1" or something like that.


    I can think of a dozen answers but I'm not quite sure what the question is. :)

    A quick screen shot might help us to help you.




  • Robin Doyle
    edited 02/07/16

    I need to add together several text/number cells across a row to populate cells in a sum column, which will be assigned as the official "duration" column for Gantt purposes. In the screenshot below, I'm wanting a formula for the total Hours column (the official duration column) that would add together the two previous columns AND display the results in hours (in this case 20h).


    Thanks in advance for your help!


    Screen Shot 2016-02-07 at 12.58.46 PM.png

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 02/08/16



    First (or second or third ...) glitch.

    If you have Dependencies on, you can't have a formula in the "official Duration column".

    If you don't have Dependencies on, you can't have Resource Management on.


    That said, if you still want a formula in a Total Duration column, using your two columns above for row 23:


    You can't use something simple like this

    =SUM([CD Hours alllocation]23:[CS Hours allocation]23)


    because your "5h" is text, not a number.

    So you need to turn each one into a number by stripping off the "h" and then adding them together. 

    For two columns, that would look something like this:


    Assuming your users always added the "h" and not " h" or "0.5d" or no "h" at all orsomething annoyingly simple but hard to predict.


    =VALUE(LEFT([CD Hours alllocation]23, LEN([CD Hours alllocation]23) - 1)) + VALUE(LEFT([CS Hours allocation]23, LEN([CS Hours allocation]23) - 1))


    To explain:

    VALUE converts text to number, if it can.

    But it can't yet, because of that "h"

    So LEFT will take the column (1st argument) and only use the number of characters determine by LEN (length - or in this case "all of the column text") -1 (to get rid of that "h")


    Do that for each column, and Bob's your uncle.

    Hope that helps.



  • Good afternoon.

    I'm having trouble getting the total duration of the phase activities.

     I would appreciate your help.

    Thank you.


This discussion has been closed.