How to sum durations of time?

Robin Doyle

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

Robin,

 

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.

 

Craig

 

Robin Doyle

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!

Robin

J. Craig Williams

Robin,

 

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.

 

Craig

Jenny Pérez

Good afternoon.

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

 I would appreciate your help.

Thank you.