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


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!