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.

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

  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, _
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

  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
    '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
    '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
  '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, _

    'All dates in Q2 (across all years)
    .AutoFilter Field:=iCol, _
                Operator:=xlFilterDynamic, _
  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! 🙂


Your email address will not be published. Required fields are marked *

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


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

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

  • John,

    I have a macro that I am filtering as a after date specified. I would like the macro to read the date from a specific cell. I can create the macro to run with the date entered into the filter. For example I would like cell A3 to be the date I want the filter to use when the macro runs.

    Is this possible?

  • Hello,
    I’m new to excel VBA programming and have a pretty big challenge ahead of me – I’ll appreciate your thoughts on that. Let me describe my problem.
    I have a data copied from some source where one of the columns is intentionally formatted in two different formats – text and date, for example there’s “01-Jan” and “0-1”. I’d like to have both of the values to be in text format so that I have “1-1” and “0-1”.
    Is there any way to replate the date format with text format and copy it to some parallel column ?

    Any help will be appreciated!

  • What code do I use I have a spreadsheet with the first column is a date. The date respresents an event that took place on that specific day. Multiple events on the same date. I wish to sort or filter and copy the date based on each date

    One date april 4th
    next april 5th

    I would like to copy these dates to their own sheet within the same workbook.

    I have 1800 cells…. possibly 140 events.
    I would like to take just the first column A and 6th column F and copy and leave the others.. not copied.


  • I have the range of the trading data form year 2000 to year 2020 now I want to filter first trading day of each from year 2000 to year 2020. If run my filter code I should get only 240 row for each month in the 20 years range. Can you please help ?

  • Hi Jon,

    I use ‘Excel 2019 pro+ german’ and all your autofilter-routines in ‘AutoFilter_Multiple_Dates_Examples’ don’t work (error 1004).
    In ‘Excel 2013 pro+ german’ ist’s all the best and work fine – what’s the matterr in Excel 2019?
    Regards from germany,

  • Hi Jon, I am trying to apply a filter for a user-determined date range. The user enters a start date & end date on a form and I have variables set up to capture those inputs. The variables are formatted as dates and look the same as the data in the table. When I apply the filter substituting the variables in place of fixed dates, all rows are hidden. I tried mapping the dates to their underlying numerical value and then created additional variables to store the numerical value in memory and then I thought I could apply the filter to that column instead but the vlookup function can’t find my dates in the list (maybe because I substituted the original variables for a fixed date?). Can VBA handle a date filter to be determined by user input?

  • i want to find multiple dates in a excel sheet and replace with adding days to them
    e.g. want to find dates like this Jun/1/2021 and replace with adding 10 days Jun/11/2021

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter