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

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

excel advanced filter date range


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.

Use Sort & Filter Option to Apply Advanced Filter on Date Range

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

Use Sort & Filter Option to Apply Advanced Filter on Date Range

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

Apply Excel VBA to Use Advanced Filter For Date Range

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.

Apply Excel VBA to Use Advanced Filter For Date Range

=”>=”&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

Apply Excel VBA to Use Advanced Filter For Date Range

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

Apply Excel VBA to Use Advanced Filter For Date Range


Download Practice Workbook

Download the following workbook.


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

Get FREE Advanced Excel Exercises with Solutions!
Sudipta Chandra Sarker
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo