Display next upcoming date from list of dates

English
,
#1

My goal is to display a date that is the next upcoming from a set list of dates.

Example:
Upcoming dates are

  • 2025-11-01 (in cell A1)
  • 2025-12-01 (in cell A2)
  • 2026-01-01 (in cell A3)

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

  • NEXTDATE(2025-01-01;2025-02-01;2025-03-01) or
  • NEXTDATE(A1:A3)
#2

This is a job for VLOOKUP function where one of the arguments is TODAY().

#5

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

#3

Hallo

=MINIFS(A1:A100;A1:A100; ">=" & TODAY() )
2 Likes
#7

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.

  • Func_Range: Area of which the minimum will be calculated
  • Range1: Area to which the criteria is applied.
  • Criterion1: The criteria to be applied.
=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)

#8

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?

#4

=XLOOKUP(TODAY();A1:A50;A1:A50;;1)

2 Likes
#6

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.

  • Search criterion: Value of TODAY()
  • Search Array: Where the result will be searched for
  • Result Array: “reference of the array or range to return” (so min and max values?)
  • Result if not found: Value if lookup fails (Not used here)
  • Match Mode: Type of match (1 = “attempt exact match. If Search criterion value is not found, then return the next larger item.”)
  • Search Mode: How the value is looked up in the Search Array (Not used here)
=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)

#9

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?

#10

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.