Return Earliest Date in Column that's in the future and cell isn't blank
Hi All,
Trying to create a dashboard for video projects and I want to show the next "Edit Due Date" on the dashboard, but not all projects have a due date filled out yet, and I'm getting blanks returning on the dashboard (which is causing confusion for the team).
Started here: =MIN(COLLECT({Edit Due Date}, {Edit Due Date}, >=TODAY()))
Then tried: =MIN(COLLECT({Edit Due Date}, {Edit Due Date}, @cell <> "", {Edit Due Date}, >=TODAY()))
But that's still returning blank cells.
Best Answer
-
I think I figured out what's going on. If there aren't any dates that are beyond today in the source data, the second formula still returns a blank, because I think it's just defaulting to the topmost cell in the source data column. So I guess I need to create an IF statement that would return "No Project Scheduled" if there aren't any dates in the future.
This does it:
=IF(COUNTIF({Edit Due Date}, >=TODAY()), MIN(COLLECT({Edit Due Date}, {Edit Due Date}, @cell <> "", {Edit Due Date}, >=TODAY())), "No Project Scheduled")
Answers
-
Are you able to provide some screenshots where the source data contains a date but the second formula isn't pulling it?
-
I think I figured out what's going on. If there aren't any dates that are beyond today in the source data, the second formula still returns a blank, because I think it's just defaulting to the topmost cell in the source data column. So I guess I need to create an IF statement that would return "No Project Scheduled" if there aren't any dates in the future.
This does it:
=IF(COUNTIF({Edit Due Date}, >=TODAY()), MIN(COLLECT({Edit Due Date}, {Edit Due Date}, @cell <> "", {Edit Due Date}, >=TODAY())), "No Project Scheduled")
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!