# How to filter rows between two dates in Excel

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.

**Excel:** `=FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2))` (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 | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Order | Date | Customer | Region | Amount |  | Start | 2026-03-01 |
| SO-1041 | 2026-02-23 | Northwind | West | 1840 |  | End | 2026-03-31 |
| SO-1042 | 2026-03-02 | Contoso | East | 420 |  |  |  |
| SO-1043 | 2026-03-09 | Fabrikam | West | 395 |  |  |  |
| SO-1044 | 2026-03-15 | Litware | North | 1720 |  |  |  |
| SO-1045 | 2026-03-21 | Adatum | South | 2210 |  |  |  |
| SO-1046 | 2026-03-31 | Proseware | East | 1955 |  |  |  |
| SO-1047 | 2026-04-06 | Wingtip | West | 610 |  |  |  |
| SO-1048 | 2026-04-14 | Tailspin | South | 380 |  |  |  |
| SO-1049 | 2026-04-20 | Lucerne | North | 1100 |  |  |  |
| SO-1050 | 2026-04-28 | Contoso | East | 865 |  |  |  |

Formula in G4: `=FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2))`

Result (spills 5 × 5): SO-1042, 3/2/2026, Contoso, East, $420; SO-1043, 3/9/2026, Fabrikam, West, $395; SO-1044, 3/15/2026, Litware, North, $1,720; SO-1045, 3/21/2026, Adatum, South, $2,210; SO-1046, 3/31/2026, Proseware, East, $1,955

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

## Other ways

- `=FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=H2),"No orders")`: 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))`: 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 `<=H2` misses orders placed on the end date after midnight. Compare with `<H2+1`.

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

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/filter-rows-between-two-dates