# How to filter data by month in Excel

Test the month and the year of each date: `=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1)))` returns the rows in the same month as the date in `H1`.

**Excel:** `=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1)))` (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 |  | Month of | 2026-04-01 |
| SO-1041 | 2026-02-23 | Northwind | West | 1840 |  |  |  |
| 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 G3: `=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1)))`

Result (spills 4 × 5): SO-1047, 4/6/2026, Wingtip, West, $610; SO-1048, 4/14/2026, Tailspin, South, $380; SO-1049, 4/20/2026, Lucerne, North, $1,100; SO-1050, 4/28/2026, Contoso, East, $865

## How it works

MONTH and YEAR run on every date in `B2:B11` and give a column of numbers each. Comparing them with the month and year of `H1` and multiplying the tests keeps only the rows where both match. Any date in April 2026 in `H1` gives the same list.

## Other ways

- `=FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=EOMONTH(H1,0)))`: A date range from the first to the last day of the month. Needs H1 to be the 1st, but it's faster on very large tables.
- `=FILTER(A2:E11,TEXT(B2:B11,"yyyy-mm")=TEXT(H1,"yyyy-mm"))`: Compares year and month as one piece of text.

## Common mistakes

- Testing MONTH alone mixes years: April 2025 and April 2026 both return 4. Add the YEAR test unless the data covers one year.
- Typing a month name in `H1` ("April") doesn't work with MONTH. Use a real date, or compare `TEXT(B2:B11,"mmmm")=H1`.

## Questions

**How do I filter the current month?**

Use TODAY in place of H1: `=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(TODAY()))*(YEAR(B2:B11)=YEAR(TODAY())))`.

**How do I filter by quarter?**

Turn each month into a quarter: `=FILTER(A2:E11,ROUNDUP(MONTH(B2:B11)/3,0)=2)` returns April to June.

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-data-by-month