Thread: local settings affect autofilter dates with VBA

This is more for the benefit of my British & European chums:

When providing a date to autofilter via VBA you must put it in American order. This I think, is because of the way Excel converts the date to a string to put it into the filter 'behind the scenes' (hence not localised properly).

This wasn't working:

with range...
    .AutoFilter 11, ">" & Date - 60

Although the autofilter created looked like it ought to work, indeed recreating the exact same filter manually did work. I found that as the date was > 12 the month & date were effectively coerced into the right place. When I changed the maths to return a day of month less than 12 I saw that the day & month swapped in the custom filter created.
This does work (showing my thought process):

    .AutoFilter 11, ">" & Month(Date - 60) & "/" & Day(Date - 60) & "/" & Year(Date - 60)

which can be tidied up:

    .AutoFilter 11, ">" & Format(Date - 60, "mm/dd/yyyy")

HTH

Try converting the date to a Long

with range...
    .AutoFilter 11, ">" & CLng(Date) - 60