Extract records between two dates
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:
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)
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
- 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)
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
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 […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
Highlight specific time ranges in a weekly schedule
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
7 Responses to “Extract records between two dates”
Leave a 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.
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
Tom,
I have updated the article. Let me know if you got it to work?
Sir
What is table 13 here?
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
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?
how to add data for Table13
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/