What formula would I use to find the Task Name(Column 1) with the soonest due date?

I set up a checkbox Helper column to be checked if the Status column of the task is "not started" or "in progress". I would like the sheet summary to reference the Task Name with the soonest date in the Due Date column if the Helper column is checked. Is there a formula that would accomplish this?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Sara Ross

    Yes! You can use the MIN function to find the earliest/soonest date in a column, combined with the COLLECT function to add your criteria of the checked box.

    Then you can use a JOIN function to return multiple Task Names (or just one) that have that MIN date.

    Formula Structure:

    =JOIN(COLLECT(Data to Return, Column with Criteria, Criteria))), ",")

    Your Criteria will be the MIN(COLLECT combination.


    Try something like this:

    =JOIN(COLLECT([Task Name]:[Task Name], [Due Date]:[Due Date], MIN(COLLECT([Due Date]:[Due Date], [Helper Checkbox]:[Helper Checkbox], 1))), ",")



    So then if there were two results with that date it would return both:


    Let me know if this works for you!

    Cheers,

    Genevieve

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Sara Ross

    Yes! You can use the MIN function to find the earliest/soonest date in a column, combined with the COLLECT function to add your criteria of the checked box.

    Then you can use a JOIN function to return multiple Task Names (or just one) that have that MIN date.

    Formula Structure:

    =JOIN(COLLECT(Data to Return, Column with Criteria, Criteria))), ",")

    Your Criteria will be the MIN(COLLECT combination.


    Try something like this:

    =JOIN(COLLECT([Task Name]:[Task Name], [Due Date]:[Due Date], MIN(COLLECT([Due Date]:[Due Date], [Helper Checkbox]:[Helper Checkbox], 1))), ",")



    So then if there were two results with that date it would return both:


    Let me know if this works for you!

    Cheers,

    Genevieve

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

  • Sara Ross
    Sara Ross ✭✭✭✭✭

    You're awesome! Thank you. That was exactly what I was looking for.🙂

  • No problem! I'm glad I could help. 🙂

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!