Master Excel Advanced Filters: Two Proven Ways to Filter Data by Date Range

The dataset showcases the sales quantity of electronic products on different dates in January, February and March 2022.

Method 1- Use the Sort & Filter Option to Apply the Advanced Filter to a Date Range

STEPS:

• Create 3 column headers as shown below: Date, Products and Sales Qty.

• In the Date column, enter the criteria or range you want to apply. Here, extract sales after 31st January 2022.
• In F6, enter >1/31/2022.
• Click OK.

>1/31/2022 indicates the date after 31st January 2022.

To filter the data based on the criteria:

• Select the entire dataset B4:D14.
• Go to the Data tab and select Advanced in Sort & Filter.

• In Advanced Filter, select Copy to another location.
• List range is showing the selected range, as the whole dataset was selected.

• Enter $F$4:$H$5 in Criteria Range.
• Enter $F$7 in Copy to.
• Press Enter to see the output.

• You can change the criteria and apply advanced filtering based on the date range, Products or Sales Qty.

Method 2 – Applying Excel VBA to Use the Advanced Filter for a Date Range

STEPS:

• Create 2 column headers: Start and End Date.

To extract information about the sales of January 2022, enter the Start date as 1st January and the End date as 31st January.

• To declare the criteria in the VBA code, the header must be the same.
• In F8, enter the following formula:

• Click OK.

=”>=”&F5 copies the value from F5 and sets a criterion. The criterion indicates greater or equal to that value. The formula sets the criterion in F8 as greater or equal to 1st January 2022.

• In G8, enter the following formula:

• Click OK.

=”<=”&G5 copies the value in G5 and sets a criterion. The criterion is less or equal to that value. The formula sets the criterion in G8 as less or equal to 31st January 2022.

• Go to the Developer tab and click Visual Basic.

• In the Microsoft Visual Basic Application window, click Insert >> Module.

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

• Rg variable is the starting range of the dataset. CRg variable is the starting point of the criteria and DRg variable the starting place where the resultant dataset will be shown.
• DRg.CurrentRegion.Clear clears the area.
• Rg.AdvancedFilter xlFilterCopy, CRg, DRg  indicates the advanced filtering process.

• Run the code by clicking the following icon:

• The sales information for January 2022 is displayed.

Download Practice Workbook

Download the following workbook.

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