3

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

VBA AutoFilter Automate Date Filters

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.

VBA AutoFilters Guide.xlsm (100.5 KB)

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.

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 3 comments
Monette Quintanilla - October 19, 2018

Hello, I’m trying to create a macro that has you enter a date range in two popups and the macro looks at data in columns K:P and returns the values listed in columns D:F to a new sheet within your workbook. Please let me know if this can be done. I’m extremely new to writing what exact code needs to be listed in the VBA. Thank you so much!

Reply
Roopa - August 27, 2018

Hi Jon,

I need your help.
I have a table in 1 sheet of which 1 of the column is Date Column.
I want to give a range in another sheet with list of dates.
Say for e.g., in Sheet1, I have the dates for the entire month of August 2018,
in sheet 2, I’ll have few selected dates, but I’ll be adding to this everyday.
So I need to know how do i apply filter in date column of sheet1 for multiple dates available in sheet 2.

Also, can you please brief about how to use dim as list object and long and list column.

Thanks in advance.

Reply
paul markus - July 20, 2018

Hi Jon,

I followed the Xtreme VBA and macro course and learned from that.
There is a limit on the amount of data an workbook can handle, and I have lots of data with complex formulas added for trending and monitoring (with the aim to visualize trend excursions in the graphs). Works pretty well.

This sorting on date certainly helps, so now I can present a graph with the last 3 Quarters and the first of the next year in this way.
What I also want is to remove data from a whole year from the table and archive it.
So I need a way to sort and then select, cut/paste the rows to archive, then remove empty cells, and everything should still be working.
I think this might give problems with the formulas if the first row is removed (don’t know yet).
Is it difficult to select and cut/paste to another spreadsheet?

Another question that I am struggling with:
If a file has been opened by another user, then i would like to know who that user is: so I am thinking of using a form that asks for user’s 4 letter ID, put that value in cell in the workbook, lookup in a table in the workbook what the rights are.
If another user wants to access that same workbook: he should obtain a message who that user is, and allow him to open read only. I think it is not difficult, but maybe there is another option.

Another question is the following:
My graphs are based on a query pointing to the data entry sheet.
Looking in properties show the path, but if the user moves that table the query does not work of course: can I make this also dynamic?

Regards,
Paul

Reply

Leave a Reply: