How to manually calculate duration of task hierarchy

Benjamin Hornberger

How can I manually calculate the duration of a parent task in a task hierarchy, i.e. the duration (in working days or calendar days) from the earliest start date of any child task of the hierarchy to the latest finish date of any child task?

Background: I have a Gantt chart in a sheet where I had to disable dependencies because I am linking most of the dates from other sheets. With dependencies disabled, durations don't get calculated automatically.

If there are any other suggestions to work around the limitation of not being able to use dependencies and links in the same sheet, I'm open to these as well.

Thanks.

Paul Newcome

For the start you would use

=MIN(CHILDREN())

and for the end you would use

=MAX(CHILDREN())

Paul Newcome

If you use formulas to roll the child tasks up to the parent rows, you could use something along the lines of

=[End Date]@row - [Start Date]@row

or

=NETWORKDAYS([Start Date]@row, [End Date]@row)

Benjamin Hornberger

Thanks. This allows me to calculate the duration once I have the right start and end dates in the parent row.

So how do I do the "rolling up" part that you mention, specifically determine the earliest start date of any child task under the parent, and the latest end date of any child task?

Thanks for any help you can provide.

Paul Newcome

For the start you would use

=MIN(CHILDREN())

and for the end you would use

=MAX(CHILDREN())

Benjamin Hornberger

Great, thank you! Just getting started with formulas (and Smartsheet in general).

Paul Newcome

Happy to help. 👍️

I have found the Community to be a great source of information.