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


Tags:

Best Answer

  • Gabby Nepomuceno
    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

  • Genevieve P.
    Genevieve P. Employee Admin

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!