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

Sara Ross

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?

Genevieve P.

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

Genevieve P.

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

Sara Ross

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

Genevieve P.

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