In Excel for Microsoft 365, use the FILTER function to return rows whose dates meet your criteria. For example, with records in A2:C100, dates in B2:B100, and a start date in E2 plus an end date in F2, use =FILTER(A2:C100,(B2:B100>=E2)*(B2:B100<F2+1),"No matches") to return records from both dates inclusively, including any times on the end date.
The Basic Date Filter Formula
FILTER creates a separate result from a range by keeping rows where a matching condition is TRUE. In the formula above, the records occupy columns A through C, column B contains each record’s date, and E2 and F2 hold the first and last dates to include. Enter the formula in an empty cell outside the source data; Excel spills matching rows into the cells below and to the right.
The comparison B2:B100>=E2 includes the start date. The second comparison uses a date after the requested end date as its cutoff. Multiplying the two comparison results requires both conditions to be true, so the result includes dates on or after the start and before the day after the end. The “No matches” text appears if no rows meet the criteria.
This is a formula-based result, not a change to the source table. The original rows remain in place, and the result updates when the source data or date criteria change. FILTER is available in Microsoft 365 and Excel 2021 or later. In older Excel versions without FILTER, use the built-in filter controls or another approach described below.
How the Date Conditions Work
Excel stores valid dates as serial numbers, so date comparisons are numeric comparisons even though cells usually display a formatted date. The multiplication sign in the formula combines two conditions as AND: a row must meet the start-date test and the end-date test. This is a compact way to express a date range.
If your date column contains dates without times, another valid approach is to compare against the end date itself. With records in A2:C100, dates in B2:B100, and inclusive start and end dates in E2 and F2, this formula includes dates from both boundaries:
=FILTER(A2:C100,(B2:B100>=E2)*(B2:B100<=F2),"No matches")
That version can omit records on the end date if the date column also contains times. An Excel date-time is a number with a fractional part for the time. For instance, a value representing 3 p.m. is later than the value representing midnight at the start of that date. A test that requires the value to be less than or equal to the end date at midnight will not include the afternoon entry. Using a less-than cutoff of the following day avoids that issue.
Set Up the Data and Date Criteria
Keep the source range rectangular: use one header row, consistent columns, and one record per row. Put actual Excel dates in the date column and actual dates in the criteria cells. Formatting a cell to look like a date does not convert text into a date, so check that Excel recognizes the values before relying on comparisons.
For a simple setup, place the start date in E2 and the end date in F2. You can type dates into those cells or select them from a date-entry process; apply a date number format if you want them to display consistently. Make sure the start date is not later than the end date. Put the FILTER formula in a blank area with enough room for the returned columns and rows.
When the source is an Excel Table, structured references can make formulas easier to read and help the source expand as records are added. For example, suppose a table is named Sales and has columns named OrderDate, Customer, and Amount. With the start and end dates in E2 and F2, the corresponding formula is:
=FILTER(Sales,(Sales[OrderDate]>=E2)*(Sales[OrderDate]<F2+1),"No matches")
This returns whole matching table rows. If you want only selected columns, use those table columns as the first FILTER argument, keeping their row counts aligned with the date conditions.
Examples: A Month or a Specific Date
To filter one calendar month, store any date in that month in G2. The following formula calculates the month’s first day and the first day of the next month, then returns records within that interval. The layout is records in A2:C100, dates in B2:B100, and the chosen month represented by G2:
=FILTER(A2:C100,(B2:B100>=EOMONTH(G2,-1)+1)*(B2:B100<EOMONTH(G2,0)+1),"No matches")
For example, if G2 contains 15-Feb-2026, the included dates start on 1-Feb-2026 and stop before 1-Mar-2026. This includes every time on February 28 as well as the other days in the month. EOMONTH returns the last day of a month; adding one to the appropriate boundary gives the first day of the next month. If G2 is blank or contains text instead of a date, correct that input before interpreting the result.
To return records from one exact date, use a lower boundary and the next day as an exclusive upper boundary. Assume records are in A2:C100, date-times are in B2:B100, and the target date is in E2:
=FILTER(A2:C100,(B2:B100>=E2)*(B2:B100<E2+1),"No matches")
This returns all records occurring on E2, whether their times are midnight, midday, or later. It is more dependable than testing for equality when the source values may include times.
Formula Results Versus Excel’s Built-In Filter
A formula is useful when you want a live extract on another worksheet, a report that responds to date cells, or a list that feeds other calculations. It leaves the source order and source rows intact. The spilled results cannot be edited cell by cell; change the source record or the formula criteria instead. If the spill area is blocked by existing content, Excel displays a spill error, so clear the obstruction.
For a quick one-time view, use Excel’s built-in filter instead. Select a cell in the data, then choose Data > Filter. Open the dropdown in the date column and choose a date filter, such as a range or a period, where available. You can also use the dropdown’s search and selection controls. This hides rows that do not match; it does not create a separate formula-driven output.
The built-in filter is often simpler for manually exploring a table, while FILTER is better for a reusable output that updates with criteria cells. In Excel for the web, the FILTER function and table filtering are available, though the ribbon layout can differ slightly from the Windows desktop. On Mac, use the Data tab’s Filter command; menu placement and keyboard shortcuts differ from Windows, but the formula syntax is the same.
Common Date-Filter Problems
- The formula returns no matches. Check that the criteria cells contain real dates, that the start date is not after the end date, and that the source values are dates rather than text. A cell can look like a date while containing text imported from another system.
- Some end-date records are missing. If the date column contains times, use a less-than comparison against the day after the end date, not a less-than-or-equal comparison against the end date at midnight.
- The formula returns an error or does not spill. Confirm that FILTER is supported in your Excel version and that the destination spill area is empty. Check that the source and condition ranges have matching row counts; the date condition must correspond to the same rows as the records.
- Dates appear in an unexpected order or format. FILTER retains the source row order; it does not automatically sort. The displayed date style comes from cell formatting, so a correct date can appear in a different regional format without changing its underlying value.
- New records are not included. A fixed reference ending at row 100 will not include records entered below row 100. Convert the source to an Excel Table and use its structured references, or expand the ranges to cover the additional records.
For imported data, text conversion may be necessary before filtering. A reliable way to diagnose the issue is to inspect a suspicious date cell’s formula bar and test it in a spare cell with a basic comparison against a known date. Do not assume that changing the number format alone fixes text dates; number formatting changes how numbers display, not whether text is converted into a numeric date.
Choosing the Right Formula Approach
Use an inclusive start and exclusive next-day end when the date column may contain time values. Use an inclusive comparison against the end date only when you know the source contains date-only values. For a month, calculate the month boundaries rather than typing a different number of days for each month; that handles shorter months and leap years without manual adjustments.
Also decide whether a dynamic extract is actually needed. If colleagues only need to inspect or print the original table with some rows hidden, the built-in filter is straightforward. If a dashboard or summary needs the matching rows to appear elsewhere and update when the date selection changes, FILTER is a better fit. For older Excel releases without dynamic arrays, built-in filters are the simplest alternative; more complex formula-based extraction may require legacy array formulas or helper columns and varies by workbook design.
Frequently Asked Questions
Can I filter dates between two dates in Excel?
Yes. Use FILTER with a condition for dates greater than or equal to the start and less than the day after the end. That boundary pattern includes both selected dates and also includes times occurring on the end date.
Why does Excel’s date filter formula miss records from the last day?
The date cells may contain times. A comparison to an end date usually represents midnight at the start of that date, so later times do not pass a less-than-or-equal test. Use the next day as an exclusive cutoff.
Can I filter by month without typing the first and last day?
Yes. Put any valid date from the desired month in a criteria cell and use EOMONTH to calculate the month boundaries. This avoids manually entering different month lengths.
Does FILTER change or delete the original rows?
No. FILTER returns matching values in a separate spilled range. To hide nonmatching rows in the original table instead, use Data > Filter.
What if FILTER is not available in my Excel version?
Use the built-in Data > Filter controls for an interactive date selection. FILTER is available in Microsoft 365 and Excel 2021 or later; older versions do not support its dynamic-array behavior.
Related
- Excel Conditional Formatting for Date Ranges Explained
- Excel Conditional Formatting for Upcoming Dates
- Conditional Formatting Excel Dates Before a Date Rule
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Percentage Change Formula: Examples and Common ErrorsTo calculate percentage change in Excel, subtract the old value from the new value, divide by the old…
- Excel Delete Every X Rows: Safe Methods and FormulasYes. In Microsoft Excel for Microsoft 365 on Windows, add a helper column to mark every xth data…
- Excel Formula for Quarterly Dates: Examples and TipsTo calculate the first day of the calendar quarter containing a date in A2, use =DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1). For example,…
- Power Query Date Functions: A Practical GuidePower Query date functions are M-language functions for converting, extracting, comparing, and adjusting dates and times as data…
- Excel Sum by Category: Formulas and PivotTablesTo sum values by category in Excel, use SUMIF: with category names in A2:A100, amounts in B2:B100, and…
- Excel TEXT Function Format Codes ExplainedExcel TEXT format codes are patterns you pass to the TEXT function to turn a number, date, time,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.