Return Earliest Date in Column that's in the future and cell isn't blank

Dan Kadushin

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.

Dan Kadushin

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")

Paul Newcome

Are you able to provide some screenshots where the source data contains a date but the second formula isn't pulling it?

Dan Kadushin

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")

Dan Kadushin