Sudipta Chandra Sarker
In this article, we will learn how to use the advanced filter for the date range in Excel. Assume you have sales data for one month but don’t want to know about sales that occurred on each day of that month. Rather, you must understand what occurred on specific days or weeks. You should filter out a date range for this purpose so that you can easily determine the state of the business during that time period. So, without any delay, let’s start the discussion.
Download Practice Workbook
To practice by yourself, download the following workbook.
What Is Advanced Filter in Excel?
Advanced Filter is used in Excel for filtering with advanced criteria. Here, you can declare any criteria and extract information based on these criteria. You can also extract unique records using Advanced Filters in Excel.
2 Easy Ways to Use Advanced Filter for Date Range in Excel
To demonstrate the method we will work on the following dataset. It shows the sales quantity of some electronic products in a shop on different dates in the months of January, February and March of 2022. We will show you how to filter the data based on the date range. We will do our filtering using Advanced Filter in Excel.
1. Use Sort & Filter Option to Apply Advanced Filter on Date Range
Using Sort & Filter option, we can easily apply Advanced Filter on Date Range. Here, you have to insert the date range and you can extract the information inside this time frame. Sort & Filter can also be used to filter data based on other criteria rather than date range. Let’s follow the steps to learn the method.
STEPS:
- Firstly, create another 3–column header similar to the main dataset.
- Here, we have put the header name as Date, Products and Sales Qty.
- After that, under the Date column, write the criteria or range that you want to apply.
- We want to extract sales after 31st January 2022.
- So, we will filter our dataset from the 31st of January 2022 to the Present date range.
- For this reason, in the F6 cell, write >1/31/2022.
- Then, press OK to proceed.
Here, >1/31/2022 indicates the date after 31st January 2022.
- In the following step, we will filter out the data based on the criteria.
- So, select the entire dataset from the B4 to D14 range.
- After that, go to the Data tab and select Advanced from the Sort & Filter group.
- Instantly, a dialog box named Advanced Filter will open up.
- Then, select Copy to another location so that extracted data is copied to another location.
- As you have selected the dataset at the start, List range is showing the range of the selected dataset.
- If you do not select the dataset before, you have to write $B$4:$D$14.
- Here, $B$4:$D$14 indicates the range between B4 to D14.
- After that, write $F$4:$H$5 in the Criteria Range field.
- Here, the F4:H5 range indicates the condition of the filtering.
- Then, write $F$7 in the Copy to field.
- Here, H7 indicates the cell where the filtered data will take place.
- Now, press Enter to see the outcome.
- As a result, you can watch the filtered data.
- Here, we have filtered sales after 31st January 2022.
- You can change the criteria and can do advanced filtering based on date range.
- Similarly, you can do an advanced filter based on Products or Sales Qty.
- Then, you need to insert criteria in the Products or Sales Qty. column.
Read More: How to Apply the Advanced Filter to Copy to Another Location in Excel
Similar Readings
- How to Use Custom Autofilter in Excel for More Than 2 Criteria
- Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)
- VBA to Copy Data to Another Sheet with Advanced Filter in Excel
- How to Use Advanced Filter to Copy Data to Another Sheet in Excel
- Apply Advanced Filter Based on Multiple Criteria in One Column in Excel
2. Apply Excel VBA to Use Advanced Filter for Date Range
Applying VBA code you can also do the task of the advanced filter date range. Here, you have to insert the date range and you can extract the information inside this time frame. Let’s follow the steps below to learn the method.
STEPS:
- Firstly, create another 2–column header right side of the main dataset.
- Here, we have put the Start and End Date.
- Suppose, we want to extract information on the sales of January 2022.
- For this reason, we have put the Start date as 1st January and the End date as 31st January.
- But, for declaring criteria in our VBA code, the header needs to be the same.
- That’s why create another 2–column header named Date.
- After that, in the F8 cell, write the following formula:
=">="&F5
- Then, press OK to proceed.
Here, =”>=”&F5 copies the value from the F5 cell and sets a criterion. The criterion indicates greater or equal to that value. So, this formula sets the criterion in the F8 cell as greater or equal to 1st January 2022.
- Similarly, in the G8 cell, write the following formula:
="<="&G5
- Then, press OK to proceed.
Here, =”<=”&G5 copies the value from the G5 cell and sets a criterion. The criterion indicates lesser or equal to that value. So, this formula sets the criterion in the G8 cell as lesser or equal to 31st January 2022.
- In the next step, we will write open the Visual Basic window and write the VBA code.
- For this reason, go to the Developer tab and click Visual Basic.
- Instantly, the Microsoft Visual Basic Application window will open up.
- In the window, click on Insert >> Module to open the module.
- As a result, the module will open up and write the following code in the module:
Sub myAdvancedFilterForDateRange()
Dim eb As Workbook
Dim es As Worksheet
Dim Rg As Range
Dim CRg As Range
Dim DRg As Range
Set eb = ThisWorkbook
Set es = eb.Worksheets("VBA Code")
Set Rg = es.Range("B4").CurrentRegion
Set CRg = es.Range("F7").CurrentRegion
Set DRg = es.Range("F10")
DRg.CurrentRegion.Clear
Rg.AdvancedFilter xlFilterCopy, CRg, DRg
End Sub
- Here, Rg variable indicates the starting range of the dataset. CRg variable denotes the starting point of the criteria and DRg variable indicates the starting place where the resultant dataset will be shown.
- Next, DRg.CurrentRegion.Clear command clears the region so that data can be placed here safely.
- Then, Rg.AdvancedFilter xlFilterCopy, CRg, DRg line indicates the advanced filtering process.
- To see the outcome, we need to run the code.
- Therefore, click on the following icon to run the code.
- As a result, we can see the sales information for January 2022.
Read More: Excel VBA Examples with Advanced Filter Criteria (6 Cases)
Conclusion
In this article, we have demonstrated how to use Excel Advanced Filter for Date Range. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
Related Articles
- How to Use Auto Filter and Advanced Filter in Excel
- Use Advanced Filter with Wildcard in Excel
- How to Remove Advanced Filter in Excel (5 Effective Ways)
- Advanced Filter with Multiple Criteria in Excel (15 Suitable Examples)
- How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)
- Use Advanced Filter for Unique Records Only in Excel
- How to Use Advanced Filter If Criteria Range Contains Text in Excel
