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.

Find more posts tagged with

Accepted answers

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

All comments

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.