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

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

**Excel:** `=MINIFS(B2:B9,B2:B9,">"&E1)` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Event | Date |  | As of | 2026-07-15 |
| Board meeting | 2026-09-10 |  | Next date |  |
| Payroll run | 2026-07-31 |  |  |  |
| Quarterly review | 2026-07-02 |  |  |  |
| Audit kickoff | 2026-08-17 |  |  |  |
| Team offsite | 2026-07-22 |  |  |  |
| Budget deadline | 2026-06-30 |  |  |  |
| Product launch | 2026-10-05 |  |  |  |
| Insurance renewal | 2026-07-15 |  |  |  |

Formula in E2: `=MINIFS(B2:B9,B2:B9,">"&E1)`

Result: 7/22/2026

## 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".

## Other ways

- `=XLOOKUP(E1+1,B2:B9,B2:B9,,1)`: Match 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)`: Returns 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/next-date-in-a-list