FILTER by Date in Excel: Month, Range or Recent

To filter by date in Excel with a formula, use FILTER with a test on the date column, such as "on or after March 1 and on or before March 31". The formula returns every matching row and updates when the data or the dates change. This guide covers a date range, a single month, the last 30 days, and the problems that make date filters return nothing.

The basic formula to filter by date#

FILTER returns the rows of a range where a test is TRUE. For dates, the test compares the date column with a date:

=FILTER(array, include, [if_empty])

• array is the data you want back, such as A2:C9.
• include is the test, such as A2:A9>=DATE(2024,3,1).
• if_empty (optional) is what to show when nothing matches.

FILTER needs Excel 2021, Excel 2024 or Microsoft 365. The FILTER function guide lists every argument, and the beginner's guide to FILTER covers non-date tests.

All the examples use this order list in A1:C9:

How to filter between two dates#

To keep rows between a start and an end date, write two tests, wrap each in brackets and multiply them. Multiplying means AND: a row passes only if both tests are TRUE.

=FILTER(A2:C9,(A2:A9>=DATE(2024,3,1))*(A2:A9<=DATE(2024,3,31)))

The result is the four March orders: Birch 850, Cedar 2,300, Acme 640 and Delta 1,750. DATE(2024,3,1) builds the date from year, month and day, so it works the same in every regional date format. Don't type "3/1/2024" in quotes: that is text, not a date.

Use cells for the start and end dates#

Hard-coded dates mean editing the formula every month. Put the start date in E2 and the end date in F2, then:

=FILTER(A2:C9,(A2:A9>=E2)*(A2:A9<=F2),"No orders")

Change E2 to 15-Mar-2024 and F2 to 10-Apr-2024, and the result becomes Acme 640, Delta 1,750 and Birch 990. The "No orders" text appears if the range has no matches, instead of a #CALC! error. This is the same idea as the input cells in summing by a date range.

How to filter by month or year#

To filter one month, test the month number and the year with MONTH and YEAR:

=FILTER(A2:C9,(MONTH(A2:A9)=3)*(YEAR(A2:A9)=2024))

This returns the same four March rows. Always include the year test. Without it, a list that covers several years returns March 2023, March 2024 and March 2025 together.

For a whole year, one test is enough: =FILTER(A2:C9,YEAR(A2:A9)=2024). For a quarter, compare the month with a range: (MONTH(A2:A9)>=4)*(MONTH(A2:A9)<=6) for April to June.

Pick the month from a cell#

If E2 holds any date in the month you want, EOMONTH can build the first and last day for you:

=FILTER(A2:C9,(A2:A9>=EOMONTH(E2,-1)+1)*(A2:A9<=EOMONTH(E2,0)))

EOMONTH(E2,-1) is the last day of the month before, so adding 1 gives the 1st of the month. EOMONTH(E2,0) is the last day of E2's month. Type 15-Apr-2024 in E2, and the filter shows the three April orders: Birch 990, Cedar 1,400 and Delta 2,050. Nobody needs to know how many days each month has.

The range method in the previous section is a little faster on big data, because Excel doesn't need to run MONTH on every row. Both give the same answer.

How to filter recent dates, like the last 30 days#

For a rolling window, compare the dates with TODAY, which returns the current date:

=FILTER(A2:C9,A2:A9>=TODAY()-30)

This keeps orders from the last 30 days, and the window moves forward every day. A few variations:

To show the newest rows first, wrap the result in SORT: =SORT(FILTER(A2:C9,A2:A9>=TODAY()-30),1,-1). The 1 sorts by the first column (the date) and -1 means descending.

How to filter by date and another condition#

Add more brackets to the multiplication for more AND conditions. March orders from Acme only:

=FILTER(A2:C9,(A2:A9>=E2)*(A2:A9<=F2)*(B2:B9="Acme"))

With March in E2:F2, that returns one row: 19-Mar-2024, Acme, 640. For OR logic, add the tests with + instead of multiplying. The guide to FILTER with multiple criteria explains both in detail, and FILTER with text that contains a word covers partial matches.

Why is my date filter wrong or empty?#

Date filters fail for a few predictable reasons. Check these in order:

1. Dates stored as text. Imported dates often sit on the left of the cell and don't change with the number format. Excel treats text as larger than any number, so >= tests pass and <= tests fail. Convert the column with DATEVALUE or Data > Text to Columns.
2. Dates with times. A cell holding 31-Mar-2024 2:30 PM is larger than 31-Mar-2024, so <=DATE(2024,3,31) drops it. Use <DATE(2024,4,1) instead: "before the next day".
3. Dates typed as text in the formula. A2:A9>="1/3/2024" compares with text. Use DATE or a cell.
4. Results show as numbers. 45355 is just 4-Mar-2024 as a serial number. Format the first column of the result as Short Date.
5. #SPILL! error. The cells below or to the right of the formula aren't empty. Clear them.

Once the rows are right, you can total them. See how to sum or count FILTER results, which returns 5,540 for the March amounts above.

Practise it#

Date ranges are one of the most common filter tasks in finance and operations tests. Practise FILTER in a live spreadsheet: you type the formula and it checks it instantly. Then practise dates in criteria with COUNTIFS and SUMIFS to total a date range. Try the March exercise on this page first.