How to manually calculate duration of task hierarchy

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.

Find more posts tagged with

Accepted answers

Paul Newcome

For the start you would use

=MIN(CHILDREN())

and for the end you would use

=MAX(CHILDREN())

All comments

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

=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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.