Extract records between two dates

Author: Oscar Cronquist Article last updated on February 16, 2018

Question: How to filter rows using dates?

Answer:

In this post I will describe how to:

Filter rows using array formulas (dynamic)
Filter rows using excel table
Filter rows using excel table and vba (dynamic)

Filter rows using array formulas

Array formula in cell D2:

=INDEX($A$2:$B$38, SMALL(IF(($A$2:$A$38=$H$1), MATCH(ROW($A$2:$A$38), ROW($A$2:$A$38)), ""), ROW(A1)), COLUMN(A1))

How to create an array formula

Select cell D2
Type the array formula
Press and hold Ctrl + Shift
Press Enter once
Release all keys

How to copy array formula

Select cell D2
Copy cell D2 (Ctrl + c)
Select cell D2:E9
Paste (Ctrl + v)

How to remove #num errors

=IFERROR(INDEX($A$2:$B$38, SMALL(IF((A2:$A$38<$H$2)*(A2:$A$38>$H$1), MATCH(ROW(A2:$A$38), ROW(A2:$A$38)), ""), ROW(A1)), COLUMN(A1)),"")

Get excel sample file for this tutorial.

filter-records-between-two-dates.xls
(Excel 97-2003 Workbook *.xls)

Filter rows using an excel table

Convert range to a table

Select range A2:B38
Go to tab "Insert"
Press with left mouse button on "Table"
Press with left mouse button on OK!

Filter dates

Press with left mouse button on Black arrow near Date header
Hover over "Date filters"
Press with left mouse button on "Between..."
Select dates
Press with left mouse button on OK!

Filter rows using excel table and vba

In this example, you can type a date in cell B1 and B2. Press the button and the table is instantly filtered. Copy the vba code below into a standard module. Create a button and assign the macro.

VBA code

Sub TblFilterDates()

Worksheets("Sheet3").ListObjects("Table13").Range.AutoFilter _
Field:=1, Criteria1:=">=" & Worksheets("Sheet3").Range("B1") _
, Operator:=xlAnd, Criteria2:="<=" & Worksheets("Sheet3").Range("B2")
End Sub

Where to copy vba code?

Press Alt+F11
Insert a new module
Copy paste code into code window

How to create a button

Press with left mouse button on "Developer" tab
Press with left mouse button on "Insert" button
Create a button (form control)
Press with right mouse button on on the button
Assign your macro

Get excel sample file for this tutorial.

filter-records-between-two-dates.xlsm
(Excel 2007 MacroEnabled Workbook *.xlsm)

Filter records category

Match two criteria and return multiple records
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.

Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Extract records where all criteria match if not empty
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Extract all rows that contain a value between this and that
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

How to extract rows containing digits [Formula]
This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

Extract records containing negative numbers
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

Filter records based on a date range and a text string
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

Functions in this article

INDEX SMALL IF MATCH ROW COLUMN IFERROR TblFilterDates Worksheets

More than 1300 Excel formulas

Excel formula categories

Average Choose Combine Merge Compare Concatenate Conditional Formatting Count Count values Dates Duplicates Extract Filter Filter records Filtered values Filterxml Hyperlink If cell Index Match Logic Lookups Match Max Min Numbers in sum OR logic Overlapping Partial match Records Sequence Small Sort by Sort values Sum Sumifs Sumproduct String manipulation Time Unique distinct records Unique distinct values Unique records Unique values Vlookup Vlookup return values

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

7 Responses to “Extract records between two dates”

Tom says:

September 15, 2011 at 8:33 am

This is a great piece of programming, but I cannot for the life of me get it to work in my spread sheet. I have tried naming the ranges, adding dollar signs to keep the references constant, etc., but no matter what I do, it will not filter the date range correctly.

This is the perfect solution to my need, so if there is anything you can do to help me, I would appreciate it. I could send you the spreadsheet if that would help.

Thanks

Tom

Reply
Oscar says:

September 16, 2011 at 10:06 am

Tom,

I have updated the article. Let me know if you got it to work?

Reply
AVIUSHEK says:

October 12, 2011 at 5:15 am

Sir
What is table 13 here?

Reply
Oscar says:

October 12, 2011 at 12:03 pm

AVIUSHEK,

Table 13 is the table name.

How to find the table name
1.Select your table
2.Press with left mouse button on the "Design" tab on the ribbon
3.Read table name in properties window on the ribbon

Reply
scott says:

September 8, 2015 at 4:30 pm

I am trying to use your filter-records-between-two-dates.xls Excel 97-2003 Workbook *.xls and use the #num errors removal but the returned value is #NAME? instead, can you tell me what I am doing wrong?

Reply
izeblood says:

February 3, 2017 at 3:47 am

how to add data for Table13

Reply
Kadr Leyn says:

January 4, 2021 at 9:36 pm

Thanks for tutorial.

I developed a template to filter products between two selected dates using the userform. The filtered products are listed in the listbox. The selected products from the listbox are copied to the other worksheet.

It's image: https://imgur.com/DpkkYDq

To make the date selection easier, I added a date userform (second userform) instead of a calendar control. When press with left mouse button on the textboxes on the main userform, the date userform is opened and the user can easily select the date.
Also, I applied the elongation effect to the userform at the time of filtering.

Source codes and sample file at:https://eksi30.com/filter-products-between-two-dates-with-vba-userform/

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form