Identify deliverable date at the parent level

Gabby Nepomuceno

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

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!

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

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!

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