How to Use Advanced Filter for Date Range in Excel (2 Easy Ways)

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.

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 Filter in Excel.

Use Advanced Filter for Date Range in Excel: 2 Easy Ways

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 the Sort & Filter option, we can easily apply Advanced Filter on a 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 headers 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.

• 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.

2. Apply Excel VBA to Use Advanced Filter for Date Range

Applying VBA code you can also do the task of the Advanced Filter for 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 created another 2 column header named Date.
• After that, in the F8 cell, write the following formula:

• Then, press OK to proceed.

• Similarly, in the G8 cell, write the following formula:

• Then, press OK to proceed.

• In the next step, we will 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

• 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.

Download Practice Workbook

To practice by yourself, download the following workbook.

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. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

<< Go Back to Advanced Filter | Filter in Excel | Learn Excel