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

emilydc

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

KPH

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

KPH

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

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

KPH

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