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)
Download 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"
- Click "Table"
- Click OK!
Filter dates
- Click Black arrow near Date header
- Hover over "Date filters"
-
Click "Between..."
-
Select dates
- Click 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 SubWhere to copy vba code?
- Press Alt+F11
- Insert a new module
- Copy paste code into code window
- Click "Developer" tab
- Click "Insert" button
- Create a button (form control)
- Right click on the button
- Assign your macro
Download excel sample file for this tutorial.
filter-records-between-two-dates.xlsm
(Excel 2007 MacroEnabled Workbook *.xlsm)
Match two criteria and return multiple records
Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of [โฆ]
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 [โฆ]
Extract records containing digits [Formula]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the [โฆ]
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 [โฆ]
6 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.
English
Spanish
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.Click "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