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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!