Excel Extract Records Between Two Dates
To extract records between two dates in Excel, use the FILTER function with a start-date test and an end-date test, or apply a date filter to the table. FILTER creates a separate, updating list; a built-in filter is better when you want to inspect or temporarily hide rows in place.
Use FILTER to return matching records
Suppose dates are in column A and the full records occupy A2:D100. Put a start date in F1 and an end date in G1. In an empty cell, enter:
=FILTER(A2:D100,(A2:A100>=F1)*(A2:A100<=G1),"No matching records")
The first condition includes dates on or after the start date; the second includes dates on or before the end date. Multiplying the TRUE/FALSE tests works as an AND condition: a row is included only when both tests are true. FILTER returns the matching rows as a spilling array, so leave enough blank cells below and to the right for the results.
To include records after the start date but before the end date, change the operators to > and <. Most date-range requests mean inclusive endpoints, so use >= and <= unless your requirements say otherwise.
Set up dates and ranges correctly
Enter actual Excel dates in F1 and G1, rather than text that merely looks like a date. You can type a date and format the cell using Home > Number Format, or use DATE(year,month,day), such as =DATE(2025,1,1). Excel stores valid dates as serial numbers; display formatting changes how a date looks, not the underlying value.
- Keep the date range and return range the same height. In the example, both begin on row 2 and end on row 100.
- Use a range large enough for your records, but avoid mismatched endpoints. For growing data, an Excel Table is usually easier to maintain.
- Check that the date column contains dates, not a mixture of dates and text. Imported data may need conversion before comparisons work reliably.
- Make sure the start date is not later than the end date. Otherwise, no row can meet both conditions.
FILTER is available in current Microsoft 365 and newer Excel versions that support dynamic arrays. In an older Excel version without FILTER, use the built-in filter or a formula-based approach such as Advanced Filter.
Filter a table without creating a copy
For a quick view, select a cell in the dataset and choose Data > Filter. Use the arrow in the date column, then choose Date Filters and a between option if available. Enter the beginning and ending dates and confirm. Excel hides rows outside the range; it does not delete them. Clear the filter to show all records again.
Turning the data into a Table first can make this workflow more dependable. Select the data and choose Insert > Table, confirming that the table has headers when appropriate. Tables typically extend their filter controls as you add records, whereas a fixed range may not include new rows automatically.
A table filter is convenient for one-off review, sorting, or editing the original records. FILTER is useful when you need a distinct output area, a report, or a result that responds to changed date cells. The FILTER output is a formula result, not an independent copy; changes to source data or criteria update it.
Handle times and end-date boundaries
A common surprise occurs when a date column includes times. A cell displayed as 2025-01-31 might actually contain January 31 at 3:00 p.m. Excel represents a date-time as a date serial plus a fractional part for the time. A criterion of less than or equal to the end date at midnight excludes later times that same day.
For a range that includes every time on the end date, use a start-inclusive and next-day-exclusive test:
=FILTER(A2:D100,(A2:A100>=F1)*(A2:A100<G1+1),"No matching records")
This includes timestamps from the start date at midnight up to, but not including, midnight after the end date. It avoids relying on a particular last time of day, such as 11:59 p.m. If the column contains date-only values, the simpler inclusive formula is usually sufficient.
Use Advanced Filter or older-version alternatives
Advanced Filter is useful when FILTER is unavailable or when you want to copy matching rows to another location using Excel’s filtering tools. Create a criteria range with the exact date-column header, then put the start and end conditions on the same criteria row when they apply to the same field. For example, criteria such as >=1/1/2025 and <=1/31/2025 need to be entered in separate criteria columns with the same header to express AND conditions. Because date interpretation can depend on regional settings, entering criteria as formulas that refer to date cells can be safer.
Another simple alternative is a helper column. If dates are in A and the criteria are in F1 and G1, enter =AND(A2>=$F$1,A2<=$G$1) and fill it down. Filter the helper column for TRUE. This is transparent and compatible with many versions, though it adds a column and requires filling the formula for new rows.
Common problems and checks
- No results despite visible dates: Check whether the source values are real dates, whether the criteria cells are dates, and whether the year or regional date order was interpreted as intended.
- #SPILL!: Clear cells in the output area. A dynamic array cannot spill over occupied cells or certain merged cells.
- Only part of the data is returned: Extend the source ranges or use a Table-based reference so newly added rows are included.
- Unexpected missing records on the final day: The source likely contains times. Use the exclusive next-day boundary shown above.
- Formula separator errors: Some regional settings use semicolons instead of commas between function arguments. Follow the separator used by other formulas in your Excel installation.
Also distinguish filtering from extracting values permanently. A filter hides rows, while a FILTER formula displays a live result. To make a static copy, copy the returned results and use Paste Special > Values in a suitable location; consider whether copied records should retain formatting as well.
Frequently Asked Questions
Can Excel extract records between two dates automatically?
Yes. Use FILTER with conditions for dates greater than or equal to the start and less than or equal to the end. The returned list updates when the source records or date criteria change.
How do I include the entire end date when records have times?
Use a condition less than the day after the end date, such as date_range<G1+1, alongside the start-date condition. This includes all timestamps on the chosen end date.
Can I filter between dates without the FILTER function?
Yes. Use Data > Filter for an interactive view, Advanced Filter to copy matching rows, or a helper column with an AND formula and filter for TRUE.
Why does FILTER return no records?
Check that the date cells contain numeric Excel dates, the selected range includes the data rows, and the start date is not after the end date. If the source contains text dates, convert them to real dates.
Does Excel’s date filter delete rows outside the range?
No. A normal filter hides rows that do not match; clearing the filter makes them visible again. Deletion requires a separate action.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.