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
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!