Excel AutoFilters in VBA Using Dates

Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee

Filter by Date in Excel VBA

Got any Excel Questions? Free Excel Help

DATES IN AUTOFILTER CRITERIA

Using dates in AutoFilter can be tricky if not using the US date format. When/if you record a macro applying an AutoFilter to a date, you get code like:

Range("A1").AutoFilter Field:=1, Criteria1:=">12/08/2006"

However, when you playback the macro you will likely get an error, or unexpected results. This is because Excel sees the dates as a US date, not the format you use in Windows Regional Settings. One way around this problem is to pass the date to a Long Variable using the DateSerial function. All dates in Excel are stored as Long integers. If you are familiar with Excel and Dates/Times read these pages.

The DateSerial Function is invaluable to those of us that work on PCs without a US date format specified Windows Regional Settings. Its Syntax is; DateSerial(year, month, day)

If your date is being taken from existing data, say a worksheet cell, you can use the DateSerial as shown below;

Dim dDate As Date



If IsDate(Range("A1")) Then

    dDate = Range("A1")

    dDate = DateSerial(Year(dDate), Month(dDate), Day(dDate))

End if

Or, if you are typing the date into your code, simply use;

dDate = DateSerial(2006, 8, 12)

PASSING THE DATE TO A LONG VARIABLE

Now we are certain our date is being read correctly we can pass it to a Long Variable, e.g

dDate = DateSerial(2006, 8, 12)

lDate = dDate

Finally, we use the Long integer that represents our date in the AutoFilter criteria like;

Sub FilterByDate()

Dim dDate As Date

Dim strDate As String

Dim lDate As Long



    dDate = DateSerial(2006, 8, 12)

    lDate = dDate

    Range("A1").AutoFilter

    Range("A1").AutoFilter Field:=1, Criteria1:=">" & lDate

End Sub

FILTER BY EXACT DATE

While the above works for dates greater or less than a date, or dates we use, it will often fail on filtering for an exact date. For example, the code below doesn't work on my PC with a European date format.

Sub FilterByExactDateNot()

Dim dDate As Date

Dim strDate As String

Dim lDate As Long



    dDate = DateSerial(2006, 8, 12)

    lDate = dDate

	Range("A1").AutoFilter

    Range("A1").AutoFilter Field:=1, Criteria1:=lDate

End Sub

It does apply and filter the list of dates in Column "A" but no data shows even though the date does exist. The only work-around I know is to use 2 criteria and make the second date criteria 1 day after the needed date. That is like below

Sub FilterByExactDate()

Dim dDate As Date

Dim strDate As String

Dim lDate As Long



    dDate = DateSerial(2006, 8, 12)

    lDate = dDate

	Range("A1").AutoFilter

    Range("A1").AutoFilter Field:=1, Criteria1:=">=" & lDate, _

                     Operator:=xlAnd, Criteria2:="<" & lDate + 1

End Sub

FILTER BY DATE & TIME

If your data is a list of valid Excel dates & times you can use the DateSerial & TimeSerial Function combined and pass the date & time to a Double variable. We need a Double as a valid Excel date and time is seen as a decimal number, not a whole number like dates only are.

Sub FilterByDateTime()

Dim dDate As Date

Dim dbDate As Double



If IsDate(Range("B1")) Then

    dbDate = Range("B1")

    dbDate = DateSerial(Year(dbDate), Month(dbDate), Day(dbDate)) + _

         TimeSerial(Hour(dbDate), Minute(dbDate), Second(dbDate))

    Range("A1").AutoFilter

    Range("A1").AutoFilter Field:=1, Criteria1:=">" & dbDate

End If

End Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft