How to Use an Advanced Filter for a Date Range in Excel

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:

=">="&F5

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:

="<="&G5

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.