Autofilter with column formatted as date

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"

Criteria1:= ">" & CDbl([datecell])Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))

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.

Autofilter() works with 'universal' format yyyy-mm-dd, i.e.:

Criteria1:= ">" & Format([MY_DATE],"yyyy-mm-dd")Criteria2:= "<=" & Format([MY_DATE],"yyyy-mm-dd")

It's better because Excel can't 'understand' it wrong . If you use mm/dd/yyyy or dd/mm/yyyy Excel can fit 02/jan as 01/feb.

see: http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

The Rules for Working with Excel (International Issue)

2. When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.

Edit:

We can create an universal Function using Application.International like:

Sub MySub()    Select Case application.International(xlDateOrder)        Case Is = 0            dtFormat = "mm/dd/yyyy"        Case Is = 1            dtFormat = "dd/mm/yyyy"        Case Is = 2            dtFormat = "yyyy/mm/dd"        Case Else            dtFormat = "Error"    End Select    Debug.Print (dtFormat)    ...    Criteria1:= ">" & Format([MY_DATE],dtFormat)    Criteria2:= "<=" & Format([MY_DATE],dtFormat)    ...End Sub

you need to convert the format to the american format, like:">" & Format([datecell], "mm/dd/yyyy")VBA does not understand another format.