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

  • Dan Kadushin
    Dan Kadushin ✭✭✭✭
    edited 08/03/22 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")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!