local settings affect autofilter dates with VBA

[Cheeky Charlie]

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:

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

which can be tidied up:

Please Login or Register  to view this content.

HTH

[royUK]

Try converting the date to a Long

Please Login or Register  to view this content.