Spreadsheet Practice

How to find the next date after a given date in a list

Formula (Excel and Google Sheets)

=MINIFS(B2:B9,B2:B9,">"&E1)

Take the smallest date that is later than the given one: =MINIFS(B2:B9,B2:B9,">"&E1). The list can be in any order.

Result on the example
7/22/2026 in E2
Works in
Excel 2019 and later, including Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

How it works

MINIFS returns the smallest value in B2:B9 among the dates that pass the condition ">"&E1, later than July 15. The earliest of those is July 22. The insurance renewal on July 15 itself is left out because the test is "after", not "on or after".

=MINIFS(B2:B9,B2:B9,">"&E1)

Result46225

  1. B2:B98 rows × 1 col: 9/10/2026, 7/31/2026, 7/2/2026, 8/17/2026, …

    A range: 8 cells in one column.

  2. E17/15/2026

    The value in E1.

  3. ">"&E1">46218"

    The & operator joins as text.

  4. MINIFS(B2:B9,B2:B9,">"&E1)46225

    MINIFS: Smallest value that meets every condition.

    • min_rangeB2:B98 rows × 1 col: 9/10/2026, 7/31/2026, 7/2/2026, 8/17/2026, …
    • criteria_range1B2:B98 rows × 1 col: 9/10/2026, 7/31/2026, 7/2/2026, 8/17/2026, …
    • criteria1">"&E1">46218"

In Google Sheets

The same formula works in Google Sheets: =MINIFS(B2:B9,B2:B9,">"&E1). We ran it in Google Sheets mode on the same data and it returns 7/22/2026.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(E1+1,B2:B9,B2:B9,,1)7/22/2026Match mode 1 returns the next date at or after the day after E1. The list doesn't need sorting.
=XLOOKUP(E1+1,B2:B9,A2:A9,"Nothing scheduled",1)Team offsiteDifferent resultReturns the event instead of the date. The event name, Team offsite.

Common mistakes

  • When no date is later, MINIFS returns 0, which shows as 1/0/1900. Check for it: =IF(COUNTIF(B2:B9,">"&E1),MINIFS(B2:B9,B2:B9,">"&E1),"None").
  • Use ">="&E1 if an event on the given date should count as next.
  • The result cell needs a date format, or it shows a number like 46225.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Find the next date after a given date in a list

In E2, return the first event date that comes after the date in E1.

Questions

How many days until the next date?

Subtract: =MINIFS(B2:B9,B2:B9,">"&E1)-E1.

How do I find the most recent date before a date?

Use MAXIFS with "<": =MAXIFS(B2:B9,B2:B9,"<"&E1).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.