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
-
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
Answers
-
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
-
You're awesome! Thank you. That was exactly what I was looking for.🙂
-
No problem! I'm glad I could help. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!