Advanced Filter for Dates

[Aussiebear]

Apparently if you run Advanced Filter from the Ribbon, this formula works fine.

="<1/9/2021"

Yet it appears that you can't run the same formula when using Advanced Formula within VBA, namely the formula needs to be reconstructed to;

="<" & Date(2021,9,1)

Why is this so?

[Aflatoon]

VBA always runs as if you were in the US so anything that needs interpreting (like a date in text format) will be interpreted using US regional settings, so that criterion would be <9 Jan 2021 not <1 Sep 2021. Using the date formula, you can see that the literal number value is added to the criterion, so there is no room for interpretation.

[Aussiebear]

Hmmmm.... Land of Convicts 0. USA 1. Grrr...

[georgiboy]

The whole world (well left to right world) should adopt the UK format for date as it just makes more sense:

Day (changes most often/ looked at most often/ conveniently placed to the left)
then
Month (Changes second most often so next in line - left to right)
then
Year (Changes least often so pushed to the right)

:whistle:

[Aflatoon]

Hmmmm.... Land of Convicts 0. USA 1. Grrr...

Hey, it makes sense to default to a format that no other country uses... ;)