How to Filter for Dates with VBA Macros in Excel

Bottom line: Learn how to apply date filters with VBA.  Includes examples for filtering for a range between two dates, date groups from filter drop-down list, dynamic dates in period, etc.

Skill level: Intermediate

Download the File

The Excel file that contains the code can be downloaded below.  This file contains code for filtering different data types and filter types.  Please see my article on The Ultimate Guide to AutoFilters in VBA for more details.

Date Filters in Excel

We have a lot of options when filtering a column that contains dates.  The filter drop-down menu list groups the dates by years, months, days, hours, minutes, seconds.  We can expand and select those check boxes to filter multiple items.

We can also choose from the Date Filters sub menus.   This allows us to filter for date ranges like before, after, or between two dates.  There are also many options for dates in periods (This Month, Next Month, Last Quarter, etc.).

Let's take a look at some code examples for the different date filters.

When applying a filter for a single number we need to use the number formatting that is applied in the column.  This is a weird quirk of VBA that can cause inaccurate results if you don't know the rule.  There is an example in the code below.

Date Range Filters in VBA

The following macro contains examples for basic date filters.   It's important to note that the Criteria parameter values are wrapped in quotation marks.  The comparison operators = < > are also included inside the quotes.  Please see my post on The Ultimate Guide to Filters in VBA for more details on how to use the AutoFilter method and its parameters.

You can copy/paste the code below into the VB Editor.

Sub AutoFilter_Date_Examples()
'Examples for filtering columns with DATES
 
Dim lo As ListObject
Dim iCol As Long

  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  
  'Set filter field
  iCol = lo.ListColumns("Date").Index
   
  'Clear Filters
  lo.AutoFilter.ShowAllData

  With lo.Range
      
    'Single Date - Use same date format that is applied to column
    .AutoFilter Field:=iCol, Criteria1:="=1/2/2014"
    
    'Before Date
    .AutoFilter Field:=iCol, Criteria1:="<1/31/2014"
    
    'After or equal to Date
    .AutoFilter Field:=iCol, Criteria1:=">=1/31/2014"
    
    'Date Range (between dates)
    .AutoFilter Field:=iCol, _
                        Criteria1:=">=1/1/2014", _
                        Operator:=xlAnd, _
                        Criteria2:="<=12/31/2015"
                      
End Sub

Multiple Date Groups

The following macro contains examples of how to filter for multiple date groups.  This is the same as selecting specific years, months, days, hours, minutes from the list box in the filter drop-down menu.

For these filters we set the Operator parameter to xlFilterValues.  We use Criteria2 (not Criteria1) to specify an Array of items using the Array function.

This is a special patterned array where the first number is the time period (year, months, days, etc.).  The second number is the last date in the range.  The macro below contains examples and further explanation.

Sub AutoFilter_Multiple_Dates_Examples()
'Examples for filtering columns for multiple DATE TIME PERIODS
 
Dim lo As ListObject
Dim iCol As Long

  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  
  'Set filter field
  iCol = lo.ListColumns("Date").Index
   
  'Clear Filters
  lo.AutoFilter.ShowAllData

  With lo.Range

    'When filtering for multiple periods that are selected from
    'filter drop-down menu,use Operator:=xlFilterValues and
    'Criteria2 with a patterned Array.  The first number is the
    'time period.  Second number is the last date in the period.
    
    'First dimension of array is the time period group
      '0-Years
      '1-Months
      '2-Days
      '3-Hours
      '4-Minutes
      '5-Seconds
    
    
    'Multiple Years (2014 and 2016) use last day of the time
    'period for each array item
    .AutoFilter Field:=iCol, _
                Operator:=xlFilterValues, _
                Criteria2:=Array(0, "12/31/2014", 0, "12/31/2016")
    
    'Multiple Months (Jan, Apr, Jul, Oct in 2015)
    .AutoFilter Field:=iCol, _
                Operator:=xlFilterValues, _
                Criteria2:=Array(1, "1/31/2015", 1, "4/30/2015", 1, "7/31/2015", 1, "10/31/2015")
    
    'Multiple Days
    'Last day of each month: Jan, Apr, Jul, Oct in 2015)
    .AutoFilter Field:=iCol, _
                Operator:=xlFilterValues, _
                Criteria2:=Array(2, "1/31/2015", 2, "4/30/2015", 2, "7/31/2015", 2, "10/31/2015")
    
    'Set filter field
     iCol = lo.ListColumns("Date Time").Index
     
    'Clear Filters
    lo.AutoFilter.ShowAllData
    
    'Multiple Hours (All dates in the 11am hour on 1/10/2018
    'and 11pm hour on 1/20/2018)
    .AutoFilter Field:=iCol, _
                Operator:=xlFilterValues, _
                Criteria2:=Array(3, "1/10/2018 13:59:59", 3, "1/20/2018 23:59:59")
  
  End With
  
End Sub

Dynamic Dates in Period Examples

The following macro contains examples for dates in specific periods.  This is the same as selecting the preset filter items from the Date Filters sub menu.

For these filters we set the Operator parameter to xlFilterDynamic.  We set Criteria1 to a constant that represents the dynamic date period option.  The constants are listed below.

Sub AutoFilter_Dates_in_Period_Examples()
'Examples for filtering columns for DATES IN PERIOD
'Date filters presets found in the Date Filters sub menu
 
Dim lo As ListObject
Dim iCol As Long

  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  
  'Set filter field
  iCol = lo.ListColumns("Date").Index
   
  'Clear Filters
  lo.AutoFilter.ShowAllData
  
  'Operator:=xlFilterDynamic
  'Criteria1:= one of the following enumerations
  
  ' Value Constant
  ' 1     xlFilterToday
  ' 2     xlFilterYesterday
  ' 3     xlFilterTomorrow
  ' 4     xlFilterThisWeek
  ' 5     xlFilterLastWeek
  ' 6     xlFilterNextWeek
  ' 7     xlFilterThisMonth
  ' 8     xlFilterLastMonth
  ' 9     xlFilterNextMonth
  ' 10    xlFilterThisQuarter
  ' 11    xlFilterLastQuarter
  ' 12    xlFilterNextQuarter
  ' 13    xlFilterThisYear
  ' 14    xlFilterLastYear
  ' 15    xlFilterNextYear
  ' 16    xlFilterYearToDate
  ' 17    xlFilterAllDatesInPeriodQuarter1
  ' 18    xlFilterAllDatesInPeriodQuarter2
  ' 19    xlFilterAllDatesInPeriodQuarter3
  ' 20    xlFilterAllDatesInPeriodQuarter4
  ' 21    xlFilterAllDatesInPeriodJanuary
  ' 22    xlFilterAllDatesInPeriodFebruray <-February is misspelled
  ' 23    xlFilterAllDatesInPeriodMarch
  ' 24    xlFilterAllDatesInPeriodApril
  ' 25    xlFilterAllDatesInPeriodMay
  ' 26    xlFilterAllDatesInPeriodJune
  ' 27    xlFilterAllDatesInPeriodJuly
  ' 28    xlFilterAllDatesInPeriodAugust
  ' 29    xlFilterAllDatesInPeriodSeptember
  ' 30    xlFilterAllDatesInPeriodOctober
  ' 31    xlFilterAllDatesInPeriodNovember
  ' 32    xlFilterAllDatesInPeriodDecember
      
  With lo.Range
    
    'All dates in January (across all years)
    .AutoFilter Field:=iCol, _
                Operator:=xlFilterDynamic, _
                Criteria1:=xlFilterAllDatesInPeriodJanuary

    'All dates in Q2 (across all years)
    .AutoFilter Field:=iCol, _
                Operator:=xlFilterDynamic, _
                Criteria1:=xlFilterAllDatesInPeriodQuarter2
  
  End With

End Sub

Here is a link to the MSDN help page with a list of the XlDynamicFilterCriteria Enumeration.  And thanks to Doug Glancy over at YourSumBuddy for pointing out the February misspelling in the constant.  He has some good tips for the Operator parameter in this article.

Filters & Data Types

The filter drop-down menu options change based on what type of data is in the column.   We have different filters for text, numbers, dates, and colors.  This creates A LOT of different combinations of Operators and Criteria for each type of filter.

I created separate posts for each of these filter types.  The posts contain explanations and VBA code examples.

• How to Clear Filters with VBA
• How to Filter for Blank & Non-Blank Cells
• How to Filter for Text with VBA
• How to Filter for Numbers with VBA
• How to Filter for Colors & Icons with VBA

The file in the downloads section above contains all of these code samples in one place.  You can add it to your Personal Macro Workbook and use the macros in your projects.

Please leave a comment below with any questions or suggestions.  Thanks! 🙂