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

Margarethe Javellana

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!

Genevieve P.

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