Using the SMALL function for dates in the future

Tom Groves

Hi all,

I've got a yearly meeting schedule for 2024 with a particular set of forums and have been asked to provide a metric that shows the next meeting (done with the MIN function) and the 2 next meetings following that.

I've got the meetings sorted horizontally with a column for each month [Feb 24]:[Dec 24].

The next meeting has been sorted with this function -

=IFERROR(MIN(COLLECT([Feb 24]@row:[Dec 24]@row, [Feb 24]@row:[Dec 24]@row, @cell > TODAY())), "TBC")

I've been using the small function to get the next two values:

=SMALL(COLLECT([Feb 24]@row:[Dec 24]@row, [Feb 24]@row:[Dec 24]@row, >=TODAY()), 2)

=SMALL(COLLECT([Feb 24]@row:[Dec 24]@row, [Feb 24]@row:[Dec 24]@row, >=TODAY()), 3)

This works for monthly meetings, but I'm currently having issues with the forums that don't occur every month (bi-monthly or quartlerly meeting) which will have 1 date then two blanks cells before the next date is entered. These are either showing #INVALID VALUE or showing the end date in the range (skipping two meeting dates in the middle of the range).

I've tried using an additional range in COLLECT to ignore blanks but it hasnt worked for me yet.

Can anyone show me where I might be going wrong?

Paul Newcome

Can you provide the formula you have tried to use that excluded blanks?