Excel FILTER Function With Dates: Examples and Tips

Formulas & functions · Updated November 19, 2026

To filter rows by date in Excel, use the FILTER function with a date comparison as its include condition. For example, with dates in A2:A6 and the full records in A2:D6, this returns rows from January 2025: =FILTER(A2:D6,(A2:A6>=DATE(2025,1,1))*(A2:A6<DATE(2025,2,1)),"No matching rows").

What the FILTER function does

FILTER returns the rows or columns from a range that meet a condition. Unlike the dropdown filters on a table, it does not hide rows in place: it displays matching records in a separate, automatically expanding result area. This makes it useful for building summaries, reports, and live extracts that update when the source data changes.

The function’s basic form is FILTER(array,include,[if_empty]). The array is the data to return; include is a condition that produces TRUE or FALSE values; and the optional if_empty argument supplies a result when nothing matches. The ranges used for array and include need compatible dimensions. For a list of records, the include range usually has one condition per data row.

FILTER is available in Microsoft 365 and Excel 2021 or later. It is also available in Excel for the web. In current Excel for Mac versions that include FILTER, the function works on the same principles; the formulas below use the comma separators shown for Excel on Windows.

Prepare date data before filtering

Excel can compare dates reliably when the cells contain real date values. Internally, Excel stores dates as serial numbers, so a comparison such as “on or after January 1” is a numeric comparison. A cell that merely looks like a date may instead contain text, which can prevent the condition from matching as expected.

For the examples below, assume a worksheet has these columns and records:

  • A1:D1 contains the headings Date, Item, Amount, and Status.
  • Rows 2–6 contain five records dated January 5, January 18, February 2, February 15, and March 3, 2025.
  • The other fields identify an item, its amount, and whether its status is Paid or Open.

Enter dates using Excel’s date entry or a function such as DATE, rather than relying on ambiguous text like 3/4/25, which can be interpreted differently under different regional settings. You can check a suspicious cell by changing its number format to General: a real date displays as a number. Formatting alone does not convert text into a date, however. If imported dates are text, convert them to actual dates before relying on date comparisons.

Leave the heading row out of both the array and the date condition in these examples. The formulas return data rows only, so keep headings above the formula or add headings separately.

Filter a range between two dates

For a period, use two comparisons: one for the first date and one for the boundary after the last date. Multiplying the TRUE/FALSE conditions acts like AND, so a row must satisfy both. In the sample layout, this formula returns the January 5 and January 18 records:

With dates in A2:A6 and complete records in A2:D6, use:

=FILTER(A2:D6,(A2:A6>=DATE(2025,1,1))*(A2:A6<DATE(2025,2,1)),"No matching rows")

The upper boundary is February 1, not January 31. That approach includes every time on January 31 if the date cells also contain times. For date-only cells, it gives the same result as an inclusive January 31 end date, while avoiding a fragile “end of day” time value.

To make the period adjustable, put the starting date in F1 and the final date in G1. Enter actual date values in those cells. With dates in A2:A6 and records in A2:D6, this includes the whole end date in G1:

=FILTER(A2:D6,(A2:A6>=F1)*(A2:A6<G1+1),"No matching rows")

For example, if F1 is January 1, 2025 and G1 is January 31, 2025, the results are the same two January records. Adding 1 to the end date sets the boundary at midnight on the following day, so a timestamp on January 31 is still included.

Filter for a month, cutoff, or another condition

To select a month, compare the dates with the first day of that month and the first day of the next month. For February 2025, with the same data layout, the formula returns the February 2 and February 15 records:

=FILTER(A2:D6,(A2:A6>=DATE(2025,2,1))*(A2:A6<DATE(2025,3,1)),"No matching rows")

A date cutoff is simpler. If F1 contains a real date, this formula returns records dated on or after that date; replacing the comparison with a less-than condition would return earlier records. With dates in A2:A6 and records in A2:D6, use:

=FILTER(A2:D6,A2:A6>=F1,"No matching rows")

You can combine date conditions with other criteria. Multiplying conditions means AND: every condition must be true. For instance, to return only Paid records in February, use the February date boundaries and the Status values in D2:D6. With records in A2:D6, dates in A2:A6, and status values in D2:D6, use:

=FILTER(A2:D6,(A2:A6>=DATE(2025,2,1))*(A2:A6<DATE(2025,3,1))*(D2:D6="Paid"),"No matching rows")

For the sample records, that returns the February 2 record and its associated fields. To express OR between two conditions, add their TRUE/FALSE arrays instead of multiplying them. Be careful when adding conditions: if a row meets both, the sum can be 2, which still counts as TRUE for FILTER, so the row is returned once.

Dates that include times

A date-time value contains both a date and a fractional time. For example, two entries on the same calendar day can have different underlying values if one is recorded at 9 a.m. and another at 4 p.m. A condition that requires a value to be less than the date at midnight on the final day excludes both later timestamps that day. That is why the “less than the next day” pattern is useful for date ranges.

For a single day, set the lower boundary to that day and the upper boundary to the following day. For a period ending on a date in G1, use a less-than comparison against G1 plus 1, as in the adjustable-range example. If the end point is meant to be an exact time rather than a whole calendar day, use the exact time value as the upper boundary instead.

Blank date cells are another consideration. A blank can behave like zero in numeric comparisons, so a lower bound set to a modern date normally excludes blanks. If your criteria include values earlier than the lower bound, or you want to make the intention explicit, add a nonblank condition such as A2:A6<>”” and multiply it with the other conditions. Make sure that condition covers the same rows as the date condition.

Spill results, empty matches, and layout

FILTER is a dynamic-array function: it spills its results into neighboring cells and adjusts the output size as the matching records change. Keep the spill area empty. Existing values, merged cells, or other obstructions can cause a #SPILL! error. Dynamic-array formulas also cannot spill from inside an Excel table, so place the formula in a normal worksheet cell outside the table, even if the source data is formatted as a table.

If no rows match and you omit the third argument, Excel returns a #CALC! error. Supplying a message such as “No matching rows” makes the result clearer. You can use an empty string instead if you prefer a visually blank result, but a blank display can make it harder to distinguish “no matches” from a formula that has not been entered.

The output follows the order of the source records; FILTER does not automatically sort by date. If you need chronological order, sort the source data or use a separate sorting function with the filtered result. Also remember that the formula returns values from the array you specify. To return only the Date and Item columns, select those columns in the array or build a separate output range; the examples return all four columns.

Common problems and how to fix them

  • No expected rows: Check that the source dates are numeric Excel dates, that the boundaries are correct, and that the include and array ranges cover corresponding rows.
  • Unexpected dates near the end of a period: Check for times stored with the dates. Use a less-than comparison against the next day rather than a less-than-or-equal comparison against midnight on the final day.
  • #VALUE! or a malformed condition: Confirm that the include condition returns an array with the same number of rows as the data being filtered. Avoid accidentally including a heading in just one of the ranges.
  • #SPILL!: Clear cells where the results need to appear, unmerge cells in that area, and move the formula outside any Excel table.
  • Formula not recognized: FILTER is not available in older perpetual versions such as Excel 2019. Confirm the Excel version, and check that the formula separators match the regional settings on the computer.

When dates came from a CSV or another system, the display format can be misleading: text may be formatted to look like dates without becoming date values. Convert or re-import those fields as dates first. Changing the cell format by itself only changes how a value is displayed; it does not repair text data.

FILTER compared with Excel’s dropdown filters

Use FILTER when you need a separate, formula-driven result that can feed another calculation or report. It updates as the source data or criteria change, and it leaves the original list visible. Its main tradeoffs are that it requires a modern Excel version and needs an unobstructed area for the spilled output.

Use the built-in dropdown filters when you want to temporarily show or hide rows in the original list. Select the data and choose Data > Filter, then use the dropdown in the date column to select date criteria. This is convenient for inspecting a list directly, but it does not create a separate result range for another report. The two approaches solve related but different tasks, and neither changes the underlying date values.

Frequently Asked Questions

Can FILTER return dates from a specific month?
Yes. Set the lower boundary to the first day of the month and the upper boundary to the first day of the next month, using greater-than-or-equal and less-than comparisons. This pattern also handles date-time entries within the month.

Why does FILTER miss dates that look correct?
The cells may contain text rather than Excel date values, or they may include times that place them outside the comparison boundary. Check the underlying values and use the next day as an exclusive upper boundary when filtering through a full final date.

Can I use start and end dates entered in cells?
Yes. Put the start and end dates in cells, then compare the date column with those cells. If the end date should include every time on that day, compare against the end-date cell plus 1 using a less-than condition.

Does FILTER work in Excel for Mac or Excel for the web?
FILTER is available in supported Microsoft 365 and Excel 2021-or-later editions, including Excel for the web. On Mac, use a version that includes the function; the same date-comparison logic applies.

What does #CALC! mean in a date filter?
Usually, no records meet the condition and the formula has no if_empty argument. Add a third argument such as “No matching rows” to display a message instead of the error.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.