Show my "up next date" based on the child-parent task relationship?

Hi! I've been using the MIN and MAX children functions to roll up dates from child tasks, or sub-tasks, to the parent task.

I also want to create a column that looks at the dates of each sub-tasks and grabs the one closest to today's date so I know the "next upcoming due date" among a parent's children/sub-tasks. What would be the best way to write that formula? Is that possible?

If it's helpful, here's an example:

I have the parent task "Initiative 1"
It has 4 child tasks "Step 1, Step 2, Step 3, Step 4"
Step 1 is giving me my min/start date
Step 4 is giving me my max/end date
Step 1 is complete and I want to have a column that pulls step 2 date to the parent task so I know the next upcoming date or milestone on Initiative 1
How do I show Step 2's date on Initiative 1 (parent task/row) and without doing it manually?

Find more posts tagged with

Sheets

Formulas

Support

Accepted answers

All comments

Paul Newcome

Try something like this:

=MIN(COLLECT(CHILDREN(), CHILDREN(), @cell >= TODAY()))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.