# How to find which period a date falls in in Excel

List the start date of each period and use an approximate match: `=XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1)`.

**Excel:** `=XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1)` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Sprint | Starts |  | Date | 2026-03-18 |
| Sprint 1 | 2026-01-05 |  | Sprint |  |
| Sprint 2 | 2026-01-19 |  |  |  |
| Sprint 3 | 2026-02-02 |  |  |  |
| Sprint 4 | 2026-02-16 |  |  |  |
| Sprint 5 | 2026-03-02 |  |  |  |
| Sprint 6 | 2026-03-16 |  |  |  |
| Sprint 7 | 2026-03-30 |  |  |  |
| Sprint 8 | 2026-04-13 |  |  |  |

Formula in E2: `=XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1)`

Result: Sprint 6

## How it works

A period runs from its start date until the day before the next start, so the right period is the one with the latest start on or before the date. Match mode `-1` (exact or next smaller) finds exactly that: March 18 falls after the March 16 start, so the answer is Sprint 6.

## Other ways

- `=INDEX(A2:A9,MATCH(E1,B2:B9,1))`: Every Excel version. MATCH with 1 needs the start dates in ascending order.

## Common mistakes

- Store real dates, not text like "Mar 16". Text dates don't compare as dates.
- A date after the last start date returns the last period, even if it ended. Add an end date and check it if the list stops.

## Questions

**How do I return the period's end date?**

Find the next start after the date and subtract one day: `=XLOOKUP(E1+1,B2:B9,B2:B9,,1)-1`. The last period needs its own end date.

**Can I use this for fiscal months or pay periods?**

Yes. Any table of start dates works: fiscal periods, pay periods, school terms or price changes.

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/which-period-a-date-falls-in