Identify deliverable date at the parent level

I'm using these formulas to show the next milestone date:

Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT([Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], >=TODAY())), 1, 0)

Next Milestone Date: =MIN(COLLECT([Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1))

They work correctly to identify the next date regardless of parent/child level. I'd like to only pick up the dates at the "example deliverable" level (i.e., first child), not the task level (any grandchildren). Can I using something like an ANCESTOR() formula to accomplish this? I'm not sure where to put it in the formula(s).

Find more posts tagged with

Formulas

Accepted answers

Gabby Nepomuceno

Awesome, thanks @Genevieve P. ! I added the helper column (and intend to hide it as you suggested). I made a small modification to move the "Level:Level, 1" criteria later in the formula to get it to work:

Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT([Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], Level:Level, 1)), 1, 0)

Next Milestone Date: =MIN(COLLECT([Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1, Level:Level, 1))

Thanks so much!

All comments

Genevieve P.

Hi @Gabby Nepomuceno

You're exactly correct! You can use COUNT with ANCESTORS to identify row levels. If your row only has 1 Ancestor (one Parent), then it's what you're looking for.

To do this, I would suggest adding a helper column in your sheet (that you can hide, if needed). Simply add this to your helper column:

=COUNT(ANCESTORS(Deliverables@row))

Then make it a Column Formula.

Now you can use this helper column (I'll call it "level") as a criteria in your current formulas:

Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT(Level:Level, 1, [Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], >=TODAY())), 1, 0)

Next Milestone Date: =MIN(COLLECT(Level:Level, 1, [Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1))

Let me know if this works for you!

Cheers,

Genevieve

Gabby Nepomuceno

Milestone checkbox column: =IF([Expected Finish*]@row = MIN(COLLECT([Expected Finish*]:[Expected Finish*], [Expected Finish*]:[Expected Finish*], Level:Level, 1)), 1, 0)

Next Milestone Date: =MIN(COLLECT([Expected Finish*]:[Expected Finish*], Milestone:Milestone, 1, Level:Level, 1))

Thanks so much!

Genevieve P.

Hi @Gabby Nepomuceno

My apologies! You are absolutely correct, the range and criteria should be after the first reference in the Collect function. I should have had more coffee before responding. 🙂

I'm glad you were able to get it to work!

Cheers,

Genevieve

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.