Return a value based on the 2nd most recent update for a specific Project ID

I am trying to return a status based on the 2nd most recent update for a specific Project.

This is the formula I currently have: =JOIN(COLLECT{Status}, {Row ID}, [Row ID]@row, {Modified Date}, LARGE({Modified Date},2)))

This formula, of course, is pulling the 2nd to most recent modified date for the entire list, regardless of the Row ID. How do I limit it to only pulling the 2nd to most recent modified date for a specific Row ID?


Thank you!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Margarethe Javellana

    We can use the COLLECT function twice in this instance - once to find the Status based on your criteria, and then a second time to find the Modified Date specific to that ID.

    Try something like this:

    =JOIN(COLLECT({Status}, {Row ID}, [Row ID]@row, {Modified Date}, LARGE(COLLECT({Modified Date}, {Row ID}, [Row ID]@row), 2)))


    Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!