How to find newest date

NAMELESS
NAMELESS ✭
edited 04/25/25 in Formulas and Functions

Hi all, this is probably an old question asked again, but I am trying to find the newest of entries on a sheet and return the value to another sheet. Here are the details:

- PrjectSheet contains the list of the projects;
- NotesSheets contains multiple note entries per project;
- None of the columns are intentionally sorted in any way.

I need to find the newest date note per each project and enter it under the "Latest Note" and "Note Date" columns on ProjectSheet. Here is a picture of it:

image.png

Any help would be greatly appreciated. Thanks in advance.

Best Answer

Answers

  • prime_nathaniel
    prime_nathaniel ✭✭✭✭✭

    @NAMELESS you want to do a collect() of the items in your note sheet filtered by name then you can do MAX on that collection to get the max date (most recent). You can then return those values or index to reference whatever you need.

    collect just has to go into something else, even if it is a join(collect(),"") where it joins to nothing but the 1 answer.

    Principal Consultant | System Integrations

    Prime Consulting Group

    Email: info@primeconsulting.com

    Follow us on LinkedIn!

  • @prime_nathaniel, thank you for your useful hint and insight.

    Based on the information you provided, I came up with the two formulas in the following that I am referencing for anyone else who might be interested in the solution:

    On the "ProjectSheet" under the "Note Date" column, I have:

    =MAX(COLLECT({NotesSheet Date}, {NotesSheet Primary Column}, Name@row))

    On the same sheet under the "Latest note" column, I have:

    =JOIN(COLLECT({NotesSheet Note Column}, {NotesSheet Primary Column}, Name@row, {NotesSheet Date}, [Note Date]@row), "")

    The two formulas did the trick.

    Thanks again.

  • prime_nathaniel
    prime_nathaniel ✭✭✭✭✭
    Answer βœ“

    @NAMELESS anytime, glad it worked out

    Principal Consultant | System Integrations

    Prime Consulting Group

    Email: info@primeconsulting.com

    Follow us on LinkedIn!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!