Formula Help: How to exclude past dates with MIN formula?

Options
emilydc
emilydc ✭✭
edited 11/15/23 in Formulas and Functions

I am not good with formulas and haven't been able to figure this one out with usual troubleshooting... I am using the following formula to return the next upcoming Review Date (screenshots of use below).

=MIN([Next Review Date]27:[Next Review Date]30)

But I want to make it so that it doesn't include dates that are in the past -- or exclude them if the checkbox is checked -- either way is fine; I have no preference. I just want to be able to see when the next review date is at a glance if my rows are collapsed.

I asked ChatGPT for assistance and those formulas didn't work either, but here is what I tried for reference:

This one returned #UNPARSEABLE:

=INDEX(SORT(FILTER([Next Review Date]27:[Next Review Date]30, [Next Review Date]27:[Next Review Date]30 >= TODAY())), 1)


This one said #INVALID OPERATION:

=MIN(IF([Next Review Date]27:[Next Review Date]30 >= TODAY(), [Next Review Date]27:[Next Review Date]30, ""))

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/15/23 Answer ✓
    Options

    How about:

    =MIN(COLLECT([Next Review Date]27:[Next Review Date]30, [Next Review Date]27:[Next Review Date]30, >=TODAY()))

    This looks at Next Review Date rows 27 to 30 and collects those where the date is in the future (greater than or equal to today) and then finds the MIN of those dates.


    Note, if you wanted this to work for the entire column (not just rows 27-30), you can remove the row numbers from the formula like this:

    =MIN(COLLECT([Next Review Date]:[Next Review Date], [Next Review Date]:[Next Review Date], >=TODAY()))

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/15/23 Answer ✓
    Options

    How about:

    =MIN(COLLECT([Next Review Date]27:[Next Review Date]30, [Next Review Date]27:[Next Review Date]30, >=TODAY()))

    This looks at Next Review Date rows 27 to 30 and collects those where the date is in the future (greater than or equal to today) and then finds the MIN of those dates.


    Note, if you wanted this to work for the entire column (not just rows 27-30), you can remove the row numbers from the formula like this:

    =MIN(COLLECT([Next Review Date]:[Next Review Date], [Next Review Date]:[Next Review Date], >=TODAY()))

  • emilydc
    emilydc ✭✭
    Options

    @KPH It worked! Thank you so much for your help. 😀

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    You're welcome @emilydc Always happy to beat AI 😉

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!