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

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!
Answers

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

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!

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
Help Article Resources
Categories
Check out the Formula Handbook template!