My goal is to display a date that is the next upcoming from a set list of dates.
Example:
Upcoming dates are
On 2025-10-20 it would display “2025-11-01”. After 2025-11-01 it would display “2025-12-01” and so on
I’m looking for something that would work like this imaginary function
This is a job for VLOOKUP function where one of the arguments is TODAY().
I used it as described on Documentation/Calc Functions/VLOOKUP - The Document Foundation Wiki
It seems that it can’t do what I need as I can not get the next date from a list, but only an information stored in a specific cell I already know.
EDIT: Or should this be used like =XLOOKUP(TODAY();A1:A50;A1:A50;;1) ?
I’d say that =VLOOKUP(TODAY(); A1:A50; 1) would do the job.
@ajlittoz it will find the last date in a sorted list, that is not greater than TODAY(); while OP seeks for not less (or maybe even strictly greater than - that’s not stated clearly, but seems implied by the wording “next upcoming”).
Hallo
=MINIFS(A1:A100;A1:A100; ">=" & TODAY() )
A note for me how this works:
MINIFS(Func_Range; Range1; Criterion1; Range2; Criterion2; … ; Range127; Criterion127)
Will search for a the minimum value (that meets different criteria in multiple ranges) in given search area and will return that value from the search area.
=MINIFS(A1:A100;A1:A100; ">=" & TODAY() )
This will search for the minimum value in the area if A1:A100 and apply “>=” & TODAY() to the area of A1:A100.
“>=” & TODAY() means greater than or equal to (">=") concatenated with (&) the value of TODAY (TODAY() )
Source: MINIFS function (from help.libreoffice.org)
If the date has already passed (eg. Today is 2025-10-10 and 2025-10-01 has already passed) and is thereby no longer included in the list, the number is just 0 if the searched area spans over empty cells.
Is there a solution for that?
Instead of A1:A50 give the range a name like Dates and refer to the range in the formula, =XLOOKUP(TODAY();Dates;Dates;;1)
The most robust against missing rows seems to be this formula I found in an Excel question, =AGGREGATE(15;6;1/(Dates>TODAY())*Dates;1) or TODAY()-1 if you want it to display 2025-11-01 on that day.
TestLookupNextDate.ods (16.5 KB)
@EarnestAl: please tell me, in which way your sophisticated Formula is more robust against missing rows than the already given MINIFS(…) -formula?
So far I see no issues with gaps in the list of upcoming dates.
Sorry @karolus I thought I had commented on the XLOOKUP comment by Sierra1. Your formula is more robust when it comes to deleting rows or changing range extent and has the virtue of of being more straightforward.
=XLOOKUP(TODAY();A1:A50;A1:A50;;1)
A note for me how this works:
=XLOOKUP( Search criterion; Search Array; Result Array; Result if not found; Match Mode; Search Mode)
Will search for a value in the given search area and (depending on the search mode) will return a certain value from the search area.
=XLOOKUP(TODAY();A1:A50;A1:A50;;1)
This will search for the value of TODAY() in area A1:A50 with min and max values of A1:A50, does nothing if it fails and will return the value searched for (TODAY) or the next higher value.
Source: XLOOKUP Function (from help.libreoffice.org)
If the date has already passed (eg. Today is 2025-10-10 and 2025-10-01 has already passed) and is thereby no longer included in the list, the cell results in an error if the searched area spans over empty cells.
Is there a solution for that?
A completely different approach to solving the problem using standard time formulas, which is much easier than accessing a list; see sheet “Tab2”:
next terminus_042353.ods (12.9 KB)
It’s easy to adjust the staggered dates in [B1:B2] by adding them together. The cells below [B3 ff.] are simply copied by dragging downwise.
But the question was: »how to pick up the next upcoming date from a list of dates in general« and NOT how to calculate »the first day of next month« … which, btw, would be done simply by: =EOMONTH(TODAY()-1;0)+1