Tom’s Tutorials For Excel: Filtering Dates

Tom’s Tutorials For Excel: Filtering Dates

When it comes to filtering dates, a little VBA goes a long way in dealing with the nemesis of seemingly countless different formats a date can be represented in Excel.

In the picture, an obnoxious myriad of dates is shown in the Date column to demonstrate how, no matter how users enter or format their dates, you can deal with it using the below macros.



This first macro named FilterBetweenDates does what the above picture shows, which is to filter a table based on a pair of Before Date and After Date criteria.

Sub FilterBetweenDates()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

Dim StartDate As Date, EndDate As Date
Dim FilterStartDate As Date, FilterEndDate As Date
Dim LastRow As Long
Dim FilterRange As Range

StartDate = Range("B5").Value
EndDate = Range("B6").Value
LastRow = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range("A8:A" & LastRow)
FilterStartDate = _
DateSerial(Year(StartDate), Month(StartDate), Day(StartDate) - 1)
FilterEndDate = _
DateSerial(Year(EndDate), Month(EndDate), Day(EndDate) + 1)

FilterRange.AutoFilter _
Field:=1, Criteria1:=">" & CDbl(FilterStartDate), _
Operator:=xlAnd, _
Criteria2:="<" & CDbl(FilterEndDate)

Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub

This macro filters for dates before today's date:

Sub FilterDateBeforeToday()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim LastRow As Long, FilterRange As Range
LastRow = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range("A8:A" & LastRow)
FilterRange.AutoFilter Field:=1, Criteria1:="<" & CDbl(Date)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub

This macro filters for dates after today's date:

Sub FilterDateAfterToday()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim LastRow As Long, FilterRange As Range
LastRow = Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range("A8:A" & LastRow)
FilterRange.AutoFilter Field:=1, Criteria1:=">" & CDbl(Date)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub

This macro deletes rows whose filtered dates are older than 3 years ago from today:

Sub DeleteRows3YearsOld()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim FilterRange As Range, myDate As Date
myDate = DateSerial(Year(Date) - 3, Month(Date), Day(Date))
Set FilterRange = _
Range("A8:A" & Cells(Rows.Count, 1).End(xlUp).Row)
FilterRange.AutoFilter Field:=1, Criteria1:="<" & CDbl(myDate)
On Error Resume Next
With FilterRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
End With
Err.Clear
Set FilterRange = Nothing
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
29 comments on “Tom’s Tutorials For Excel: Filtering Dates
  1. Tom, my browser is Google Chrome and when i copy code from your pages, line breaks are not preserved. Can you somehow publish the code with line breaks kept? Thank you very much!

    • Tom Urtis says:

      When you say line breaks are not preserved, do you mean that the entire code is on one single line? Or instead is the underscore line continuation breaks the problem? If you email me I can send a workbook example. Chrome is usually good but I have heard people mention how their browsers see things differently on a web page.

  2. Yep, one single line when copying from Chrome. Frome example when copying from this very page. And OK when copying from MS Internet Explorer. The reare case when MSIE beats Chrome. ))))

    • Tom Urtis says:

      Thanks. I’ll look forward to comments from other Chrome users who see this; so far you are the first I have heard of it but I will keep my eye on it.

  3. Mark says:

    Hi

    I used the code in the “This macro filters for dates before today’s date” section and it worked great – thanks for that!

    I also want my macro to (separately) look at rows with a date of today though. Unfortunately, I can’t figure out how to customise the criteria to do this though. Could you point me in the right direction please?

    Also, because I would be using both sections of code in the same macro, I’m finding that it errors if I duplicate the first few lines. Is it safe to remove these or should I do it slightly differently?

    Thanks in advance.

    Mark

    • Tom Urtis says:

      Hi Mark, thanks for following my Excel examples.

      A quick one-liner to filter today’s date can be this, if for example your dates are in column H (and H being the 8th letter of the alphabet, with the table starting in column A):
      Range(“A1:H100”).AutoFilter Field:=8, Criteria1:=Date

      A longer catch-all version would be to filter between dates, that is, to filter for any single date or between a pair of dates. So, for a single date, you would enter that date in both Y1 and Z1 of this example, for dates in column P.

      Sub FilterBetweenDates()
      ‘In this example you enter the from date in cell Y1 and the end date in cell Z1.
      Dim LR&, FilterRange As Range
      Application.ScreenUpdating = False
      ActiveSheet.AutoFilterMode = False
      Dim StartDate As Date, EndDate As Date
      Dim ClosedWindowStartDate As Date
      Dim ClosedWindowEndDate As Date
      StartDate = Range(“Y1”).Value
      EndDate = Range(“Z1″).Value
      LR = Cells.Find(What:=”*”, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      Set FilterRange = Range(“P1:P” & LR)
      ClosedWindowStartDate = DateSerial(Year(StartDate), Month(StartDate), Day(StartDate) – 1)
      ClosedWindowEndDate = DateSerial(Year(EndDate), Month(EndDate), Day(EndDate) + 1)
      FilterRange.AutoFilter _
      Field:=1, Criteria1:=”>” & CDbl(ClosedWindowStartDate), _
      Operator:=xlAnd, _
      Criteria2:=”<" & CDbl(ClosedWindowEndDate)
      Set FilterRange = Nothing
      Application.ScreenUpdating = True
      End Sub

      If you are trying to duplicate the code lines that contain the variable declarations, you cannot do that. Declare variables uniquely depending on what you are doing. Now, if you have two or more macros in the same module and you want to declare a variable for each one but you don't want to keep repeating that action, instead of putting the Dim declarations inside the macro, you can enter this for example, outside of and at the top of all macros in your module:

      Public LR&
      Public FilterRange As Range

      • Mark says:

        Hi

        Thanks for the quick reply. Didn’t quite understand what you meant to get around having it twice in the same macro so I added it like this:

        Application.ScreenUpdating = False
        ActiveSheet.AutoFilterMode = False

        Range(“$A$1:$AW$5000″).AutoFilter Field:=11, Criteria1:=Date
        Set FilterRange = Nothing
        Application.ScreenUpdating = True

        This seems to work ok except that the date is in the wrong format – when I manually check the filter details, it’s put it in US format. What is puzzling me though is that the original code (below) does it in the right format.

        Application.ScreenUpdating = False
        ActiveSheet.AutoFilterMode = False
        Dim LastRow As Long, FilterRange As Range
        LastRow = _
        Cells.Find(What:=”*”, After:=Range(“K1”), _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set FilterRange = Range(“K1:K” & LastRow)
        FilterRange.AutoFilter Field:=11, Criteria1:=”<" & CDbl(Date)
        Set FilterRange = Nothing
        Application.ScreenUpdating = True

        Any ideas?

        Thanks

        Mark

        • Tom Urtis says:

          I’m lost on your question. When you wrote…
          “I also want my macro to (separately) look at rows with a date of today though.”
          …I interpreted that to mean you want to filter a table of data to show only rows with today’s date.

          How about if you re-state your question by saying what you want to do. Leave out the part of whatever should be duplicated or not included or not working, and don’t worry about the data format. Just say what data you have, and what you want it to end up like after you have (presumably) filtered it. Be sure to mention relevant information, such as the first column of data (column A or whatever it is), the column holding the date (column X or whatever it is), the row holding the header labels, and the row where the actual data starts (which should be one row below the header labels but I am covering my bases to make sure I can envision where your table is located). From that point we can decide what code works best for you.

          • Mark says:

            Hi Tom

            Sorry about that 🙂

            I have a spreadsheet with headers in Row 1 and the data starts in Row 2.

            Column K contains dates that I need to format with different colours – red if the date is older than today and orange if the date is today and green if the date is in the future.

            I’m doing this in a macro as there is a bunch of other formatting needed and this is something I have to do on a daily basis with a completely new set of data each day so Conditional Formatting isn’t really an option.

            Hope this provides a clear picture.

            Cheers

            Mark

          • Mark says:

            Oh, sorry the data starts in Column A 🙂

            • Tom Urtis says:

              Not needing an exact number because I know the count of rows will change every day which is no problem, but still asking in order top recommend an optimal solution, approximately how many rows of data is this table.

          • Mark says:

            Hi Tom

            I account for 5000 in other parts of the code but realistically, no more than 1000.

            Thanks

            Mark

            • Tom Urtis says:

              See if this does what you want:

              Sub ColorDates()
              Application.ScreenUpdating = False
              ActiveSheet.AutoFilterMode = False
              Dim LastRow As Long, FilterRange As Range
              Dim myDate As Date
              Dim StartDate As Date, EndDate As Date
              LastRow = Cells(Rows.Count, 11).End(xlUp).Row
              Range("K2:K" & LastRow).Interior.ColorIndex = xlNone
              Set FilterRange = Range("K1:K" & LastRow)
              myDate = VBA.Date
              StartDate = DateSerial(Year(myDate), Month(myDate), Day(myDate) - 1)
              EndDate = DateSerial(Year(myDate), Month(myDate), Day(myDate) + 1)
              'Today
              FilterRange.AutoFilter _
              Field:=1, Criteria1:=">" & CDbl(StartDate), _
              Operator:=xlAnd, _
              Criteria2:="<" & CDbl(EndDate)
              On Error Resume Next
              With FilterRange
              .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Interior.Color = RGB(255, 165, 0)
              End With
              Err.Clear
              'Past
              FilterRange.AutoFilter Field:=1, Criteria1:="" & CDbl(myDate)
              On Error Resume Next
              With FilterRange
              .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Interior.Color = vbGreen
              End With
              Err.Clear
              ActiveSheet.AutoFilterMode = False
              Set FilterRange = Nothing
              Application.ScreenUpdating = True
              End Sub

          • Mark says:

            Hi Tom

            When I run this and pause it at the point that the filter is applied and look at the criteria being used, it says “is after 14/10/2013” and “is before 16/10/2013”. This appears to include the 14/10 (previous) as well as the 15/10 (current) but it’s only the current date that should be coloured orange.

            The next filter appears to have “41562” as the value being filtered, which obviously doesn’t give any results.

            The end result is that both the previous (should be coloured red) and current day (should be coloured orange) are coloured orange, anything older than 14/10 (should be red) is not coloured and anything from 16/10 onwards (should be green) isn’t coloured.

            Cheers

            Mark

            • Tom Urtis says:

              The code worked when I tested it based on this you wrote:

              “Column K contains dates that I need to format with different colours – red if the date is older than today and orange if the date is today and green if the date is in the future.”

              The previous day is colored red, the current date is colored orange, and the future dates are colored green. I just tested it again right now with new data.

              Make sure there are no merged cells.

              Test it yourself again, and if you till see previous dates in column K colored orange and not red, send me your workbook.

          • Mark says:

            Hi

            I’m afraid it’s definitely not working as expected but I can’t really send you the sheet as it contains sensitive information but below are some examples of the end result:

            08/10/2013 – No Fill
            14/10/2013 – Orange
            15/10/2013 – Orange
            16/10/2013 – No Fill

            I’m not sure if it has any effect but I should probably point out that the values themselves also have times (e.g. 08/10/2013 11:10) but this is true for even the ones that are successfully filled. Also, the macro is hosted in a separate workbook to the one containing the data.

            Hope this helps.

            Cheers

            Mark

            • Tom Urtis says:

              There’s no reason I can think of as to why you are seeing what you are seeing. If you can send me a worksheet with those column K dates copied and pasted, that would suffice for the actual data you are wannting to filter. Something else is going on with those cells or the workbook. The format of the date is not important, **IF** those cells are truly dates and not text.

          • Mark says:

            I’ve just checked the format and they are in Custom – dd/mm/yyyy hh:mm format. Would that cause the problem? How can I get the file to you?

  4. Ziyan says:

    Hi ,

    I tried to follow but got a bit confusion

    How do I write the VBA for macros to autofilter 5 days before today?

    Thanks,
    Ziyan

    • Tom Urtis says:

      This will work, assuming column A. Modify for actual column.


      Sub Test2()
      'Filter only dates that are 5 days before today.
      Application.ScreenUpdating = False
      ActiveSheet.AutoFilterMode = False
      Dim FilterRangeDate As Range
      Set FilterRangeDate = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
      Dim ClosedWindowStartDate As Date, ClosedWindowEndDate As Date
      ClosedWindowStartDate = DateSerial(Year(Date), Month(Date), Day(Date) - 6)
      ClosedWindowEndDate = DateSerial(Year(Date), Month(Date), Day(Date) - 4)
      FilterRangeDate.AutoFilter _
      Field:=1, Criteria1:=">" & CDbl(ClosedWindowStartDate), _
      Operator:=xlAnd, _
      Criteria2:="<" & CDbl(ClosedWindowEndDate) Set FilterRangeDate = Nothing Application.ScreenUpdating = True End Sub

  5. Shekh Nasim says:

    i would like to filter two different dates at a time like,
    3-April-16 and 5-April-16. then what will be the code in VBA.
    NB: Date comes from a reference which is changeable.

  6. Pradeep says:

    Hi,

    If i need to put filter for todays date what is the macro code?

    Thanks,

    • Tom Urtis says:

      You can use the first macro to filter between dates. The criteria for the start date is =today()-1 and for the end date it is =today()+1 if you were putting those criteria into cells such as the example shows.

      If you don’t want to use cells to hold your criteria, you can code them directly using dateserial, example
      DateSerial(Year(VBA.date), month(VBA.date), day(VBA.date)-1) for the start date variable and +1 for the end date variable.

  7. Pradeep S says:

    HI Tom,

    Thanks for the update and it really works, but check the details scenario below

    I used the code below

    Sub FilterBetweenDates()
    Application.ScreenUpdating = False
    ActiveSheet.AutoFilterMode = False

    Dim StartDate As Date, EndDate As Date
    Dim FilterStartDate As Date, FilterEndDate As Date
    Dim LastRow As Long
    Dim FilterRange As Range

    StartDate = Range(“B3”).Value
    EndDate = Range(“B10″).Value
    LastRow = _
    Cells.Find(What:=”*”, After:=Range(“B2”), _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set FilterRange = Range(“B2:B” & LastRow)
    FilterStartDate = _
    DateSerial(Year(VBA.Date), Month(VBA.Date), Day(VBA.Date) – 1)
    FilterEndDate = _
    DateSerial(Year(VBA.Date), Month(VBA.Date), Day(VBA.Date) + 1)

    FilterRange.AutoFilter _
    Field:=1, Criteria1:=”>” & CDbl(FilterStartDate), _
    Operator:=xlAnd, _
    Criteria2:=”<" & CDbl(FilterEndDate)

    Set FilterRange = Nothing
    Application.ScreenUpdating = True

    End Sub

    ——————————
    this particular works when i enter the function called today() in the one particular cell.

    Example: I have a serious of dates in one format and one particular cell alone I enter the function today(). In this scenario the output comes.

    But the macro i am expecting is, it has to get the system date automatcially and I need the output like the date will have the format (DD/MM/YYYY). I might enter the manual date also and later change the date format to DD/MM/YYYY. So the macro should run and filter the output as today date.

    Dump of the Excel Example with cell number:

    Date (B2)
    26/06/1992 (DD/MM/YYYY) (B3)
    16/03/1992 (DD/MM/YYYY) (B4)
    30/03/1992 (DD/MM/YYYY) (B5)
    03/03/1992 (DD/MM/YYYY) (B6)
    26/07/2016 (DD/MM/YYYY) (B7)
    26/02/1992 (DD/MM/YYYY) (B8)
    26/08/1992 (DD/MM/YYYY) (B9)
    27/06/2016 (=today()) – one particular cell has the function (B10)

    If I am not clear in explaining the scenario let me know I will give a hangout call in mobile.
    My whatapp number : +91 9840032452 (Country India)

    • Tom Urtis says:

      I do not understand why you cannot do what I suggested in my previous reply, to get the date directly from the system instead of using a cell, examples with the variables I first posted:
      StartDate = DateSerial(Year(VBA.date), month(VBA.date), day(VBA.date)-1)
      EndDate = DateSerial(Year(VBA.date), month(VBA.date), day(VBA.date)+1)

      Regarding the format of the dates in the output, you can format them in the macro (modify my code as needed) to appear on the worksheet any way you want to see them for the final results.

  8. Pradeep says:

    Hi Tom,

    I have sent the excel file to your email, please check the code and correct if anything wrong.

    • Tom Urtis says:

      Your dates were text, not real dates.
      When you change them to real dates (not just so they *look* like dates, but really *are* dates, this macro works, given your example dataset:

      Sub FilterToday()
      Application.ScreenUpdating = False
      ActiveSheet.AutoFilterMode = False
      Dim FilterRangeDate As Range
      Set FilterRangeDate = Range(“B2:B” & Cells(Rows.Count, 2).End(xlUp).Row)

      Dim StartDate As Date, EndDate As Date
      StartDate = DateSerial(Year(Date), Month(Date), Day(Date) – 1)
      EndDate = DateSerial(Year(Date), Month(Date), Day(Date) + 1)

      FilterRangeDate.AutoFilter _
      Field:=1, Criteria1:=”>” & CDbl(StartDate), _
      Operator:=xlAnd, _
      Criteria2:=”<" & CDbl(EndDate) Set FilterRangeDate = Nothing Application.ScreenUpdating = True End Sub

  9. Harish says:

    Hi Tom,
    I need to filter the date formats, My scenario is to filter the date formats is most latest date to previous days it may be 1 day, 2 days, 3 days,4 days, 5 days like that up to 7 days.for that i need to create macro. Can help me for the code.please reply for it

    Thanks

Leave a Reply

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

*