How to filter rows between two dates in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2))Multiply two date tests inside FILTER: =FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2)) returns every row dated from the start date to the end date.
- Result on the example
- a 5-row list that spills from G4 in
G4 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G4. Change any input and the result updates, or edit the formula to experiment.
How it works
Each comparison gives a column of TRUE and FALSE, one per order. Multiplying the two columns turns them into 1s and 0s, and only the rows where both tests are TRUE come out as 1, so FILTER keeps the orders on or after H1 and on or before H2.
Dates are numbers underneath, so the comparison works on the real date. Change either date cell and the list redraws itself.
=FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2))Result{5 rows × 5 cols} "SO-1042", 46083, "Contoso", "East", …
A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", …A range: 10 rows × 5 columns.
B2:B1110 rows × 1 col: 2/23/2026, 3/2/2026, 3/9/2026, 3/15/2026, …A range: 10 cells in one column.
H13/1/2026The value in H1.
B2:B11>=H1{10 rows × 1 col} FALSE, TRUE, TRUE, TRUE, …The >= operator tests at least.
H23/31/2026The value in H2.
B2:B11<=H2{10 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The <= operator tests at most.
B2:B11>=H1)*(B2:B11<=H2{10 rows × 1 col} 0, 1, 1, 1, …The * operator multiplies.
FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2)){5 rows × 5 cols} "SO-1042", 46083, "Contoso", "East", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", … - condition1
B2:B11>=H1)*(B2:B11<=H2{10 rows × 1 col} 0, 1, 1, 1, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2),"No orders")Excel only | spills 5 × 5 | The third argument shows a message instead of #CALC! when no order falls in the range. Excel only: in Google Sheets wrap FILTER in IFNA instead. |
| =FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<H2+1)) | spills 5 × 5 | Use < end date + 1 when the dates carry times, so an order at 4:30 PM on the last day still counts. |
Common mistakes
AND(B2:B11>=H1,B2:B11<=H2)doesn't work: AND returns one TRUE or FALSE for the whole range. Multiply the tests instead.- Dates stored as text never pass a
>=test. If FILTER finds nothing for dates you can see, check that they are real dates (they sit on the right of the cell by default). - A timestamp later in the day is larger than the date on its own, so
<=H2misses orders placed on the end date after midnight. Compare with<H2+1.
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.
Filter rows between two dates
G4, return every order dated from the start date in H1 to the end date in H2, both days included.Questions
How do I filter by a date range and another condition?
Multiply a third test: =FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2)*(D2:D11="East")).
How do I filter the last 30 days?
Compare with TODAY: =FILTER(A2:E11,B2:B11>=TODAY()-30). The list changes every day.
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.