How to find which period a date falls in in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1)List the start date of each period and use an approximate match: =XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1).
- Result on the example
- Sprint 6 in
E2 - Works in
- Excel 2021, 2024 and 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
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.
=XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1)Result"Sprint 6"
E13/18/2026The value in E1.
B2:B98 rows × 1 col: 1/5/2026, 1/19/2026, 2/2/2026, 2/16/2026, …A range: 8 cells in one column.
A2:A98 rows × 1 col: "Sprint 1", "Sprint 2", "Sprint 3", "Sprint 4", …A range: 8 cells in one column.
-1-1Makes the value negative.
XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1)"Sprint 6"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E13/18/2026 - lookup_array
B2:B98 rows × 1 col: 1/5/2026, 1/19/2026, 2/2/2026, 2/16/2026, … - return_array
A2:A98 rows × 1 col: "Sprint 1", "Sprint 2", "Sprint 3", "Sprint 4", … - if_not_found
"Before sprint 1" - match_mode
-1-1
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1). We ran it in Google Sheets mode on the same data and it returns Sprint 6.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A9,MATCH(E1,B2:B9,1)) | Sprint 6 | 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.
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 which period a date falls in
E2, return the sprint that contains the date in E1. Each sprint runs until the next one starts.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.
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.