Multiple dates meeting criteria causing #INVALID DATA TYPE error

Dan Kadushin

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

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.

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. 👍️