Excel AutoFilters in VBA Using Dates

Filter by Date in Excel VBA

Got any Excel/VBA 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

Gallery

stars (0 Reviews)

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

Website designed by Astrava Solutions Ltd.