CODEWITHSUNDEEP
dateexcelvba
Louisa Thompson
Louisa Thompson

Reputation: 97

VBA filtering on date turns into US format

I am trying to filter on a date. Have checked and the control panel knows I am in the UK. But when I filter it - it has problems and seems to be using the US date format and not the UK. When you click on the filter it gives the dates / months correctly according to the UK system.

When I recorded the code to see what was happening the following came up. The date will be defined - I was just trying to figure out what VBA was doing when my filter wouldn't work. 

Sub dating()


ActiveSheet.Range("$A$1:$AM$442").AutoFilter Field:=38, Operator:= _
    xlFilterValues, Criteria2:=Array(2, "4/25/2014")
End Sub

I have updated the code to and it still won't filter on that date even though there is data with that date in the column. I am probably missing something obvious here.

Dim dteInvoices As Date

dteInvoices = Worksheets("front sheet").Range("f14")

Worksheets("trade details").Range("A1").AutoFilter 39, strText
             .Range("A1").AutoFilter 38, CLng(dteInvoices)

Thanks to help from http://www.ozgrid.com/VBA/autofilter-vba-dates.htm I have managed to get it to work

http://www.ozgrid.com/VBA/autofilter-vba-dates.htm

Range("a1").AutoFilter Field:=38, Criteria1:=">=" & lngDate, _
    Operator:=xlAnd, Criteria2:="<" & lngDate + 1

Upvotes: 2

Views: 4135

Answers (3)

iDevlop
iDevlop

Reputation: 25262

Not a bug. Everywhere in VBA you MUST you US conventions. Which is good: otherwise one would have to use different strings depending on user settings !
I generally use Format(MyDate, "\#mm\/dd\/yy\#") to be sure.

Upvotes: 2

MP24
MP24

Reputation: 3200

This seems to be an bug with Excel. The filter does only accept US formatted dates. It makes sense to convert your text to a date using DATEVALUE and then converting the date to a number using CLNG.

Edit: see this thread for a workaround: Filtering between two dates in pivot table using VBA. UK to US date format issue

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.

Sometimes I have found that one can use the numeric value of the date, especially when dealing with "dates between"

Criteria2:= CDbl([datecell])

or

Criteria2:= CLng(Dateserial(2014,4,25))

Note that the above need to be "real dates" and not strings that look like dates. Even a single "string date" will mess things up.

Upvotes: 0

Related Questions