<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Excel AutoFilters in VBA Using Dates

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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 withExcel 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 DateIf 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 DateDim strDate As StringDim lDate As Long    dDate = DateSerial(2006, 8, 12)    lDate = dDate    Range("A1").AutoFilter    Range("A1").AutoFilter Field:=1, Criteria1:=">" & lDateEnd 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 DateDim strDate As StringDim lDate As Long    dDate = DateSerial(2006, 8, 12)    lDate = dDate	Range("A1").AutoFilter    Range("A1").AutoFilter Field:=1, Criteria1:=lDateEnd 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 DateDim strDate As StringDim lDate As Long    dDate = DateSerial(2006, 8, 12)    lDate = dDate	Range("A1").AutoFilter    Range("A1").AutoFilter Field:=1, Criteria1:=">=" & lDate, _                     Operator:=xlAnd, Criteria2:="<" & lDate + 1End 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 DateDim dbDate As DoubleIf 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:=">" & dbDateEnd IfEnd Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

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

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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