Multiple dates meeting criteria causing #INVALID DATA TYPE error

So I'm using this formula to find the earliest date that isn't a blank out of a column, and when I only have one date that's in the column and is greater than or equal to today the formula returns the correct date or "No Project Scheduled" appropriately. However, when I have two or more dates that meet that same criteria the formula throws a #INVALID DATA TYPE error.

=IF(COUNTIF({Estimated Shoot Date}, >=TODAY()), MIN(COLLECT({Estimated Shoot Date}, {Estimated Shoot Date}, @cell <> "", {Estimated Shoot Date}, >=TODAY())), "No Project Scheduled")

Screen Shot 2022-08-12 at 1.04.49 PM.png

Find more posts tagged with

Accepted answers

Paul Newcome

Try saying "if the count is greater than zero:

=IF(COUNTIF({Estimated Shoot Date}, >=TODAY()) > 0, MIN(COLLECT({Estimated Shoot Date}, {Estimated Shoot Date}, @cell <> "", {Estimated Shoot Date}, >=TODAY())), "No Project Scheduled")

Without it (assuming there are three dates for example) you are saying

=IF(3, MIN(COLLECT(...........

The reason it works when there is one or zero is because one and zero also equal true and false.

All comments

Paul Newcome

Try saying "if the count is greater than zero:

=IF(COUNTIF({Estimated Shoot Date}, >=TODAY()) > 0, MIN(COLLECT({Estimated Shoot Date}, {Estimated Shoot Date}, @cell <> "", {Estimated Shoot Date}, >=TODAY())), "No Project Scheduled")

Without it (assuming there are three dates for example) you are saying

=IF(3, MIN(COLLECT(...........

The reason it works when there is one or zero is because one and zero also equal true and false.

Dan Kadushin

That did it, thanks Paul!

Paul Newcome

Happy to help. 👍️

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.