VBA AdvancedFilter Method, date format interpretation anomaly

Question

I have been using an Excel macro for six years now, on a monthly basis under Excel 2003 and WinXP, to filter data by date ranges. Today I ran the macro for the first time under Excel 2007 and Win 7 and came across an anomaly using the VBA AdvancedFilter Method that I do not understand.

The anomaly arises when I use a Comparison operator such as < or > in front of a date that is part of my Criteria. The Format for the Date field is "d/mm/yyyy", which is the same as my Windows Regional setting, however when I insert for example <5/04/2010 (<5-Apr-2010) as the  Filter Criteria, using the following line of code:

Range("List").AdvancedFilter Action:=xlFilterInPlace,

CriteriaRange:= Range("Criteria"), Unique:=False

 the Criteria is interpreted as <4/05/2010 (<4-May-2010). Curiously, when I manually select the Advanced Filter function directly from the Ribbon (ensuring that the Criteria Range and List Range are identical to that defined in the VBA command), the date is interpreted correctly. Further, if I execute the above command from the Immediate Window in the VBE, the date is once again misinterpreted (by my expectation) as 4-May-2010. If I remove the < or > Comparison operator, and use an exact date as the Filer Criteria (e.g. 5/04/2010), this date is interpreted correctly using the VBA AdvancedFilter Method. Also when I run the same macro using the same data under Excel 2003/WinXP, it behaves just as I expect.

Obviously it is quite simple for me to come up with a work around, but since my code has worked so reliably for so long, I would like to know what has changed to cause this anomaly?

Thanks for your time.

Answer 1

Hi,

You wrote:

> the Criteria is interpreted as <4/05/2010 (<4-May-2010). Curiously, when I manually select

> the Advanced Filter function directly from the Ribbon (ensuring that the Criteria Range and

> List Range are identical to that defined in the VBA command), the date is interpreted

> correctly. Further, if I execute the above command from the Immediate Window in the VBE,

> the date is once again misinterpreted (by my expectation) as 4-May-2010.

> If I remove the < or > Comparison operator, and use an exact date as the Filer Criteria

> (e.g. 5/04/2010), this date is interpreted correctly using the VBA AdvancedFilter Method.

> Also when I run the same macro using the same data under Excel 2003/WinXP, it behaves just as

> I expect.

> Obviously it is quite simple for me to come up with a work around, but since my code has worked

> so reliably for so long, I would like to know what has changed to cause this anomaly?

I am surprised this has worked before. VBA speaks American when it comes to number and date formatting.

If your cell contains a date when VBA reads the cell, it reads the correct date. It converts the date to

US format internally so to speak, before setting the filter.

If however your cell contains a date concatenated with a > symbol, e.g. >4/5/2009 (4th of may), Excel

reads a STRING, which it then translates into the > and a date *using US syntax*. Your 4th of May becomes

5th of April. Try if changing the date format in the criteria cell to yyyy/mm/dd helps resolve the issue.

---

Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

Answer 2

I would like to offer a big thank you to Jan Karel Pieterse whose suggestion above to change the date format to 'yyyy/mm/dd' when using Advanced Filter in Excel 2007 VBA, solved a frustrating problem for me. I too had a criteria range that worked fine in Excel 2003 but failed in 2007 (maybe because of default date format rather than anything inherent in Excel 2007).

Thanks Jan! Ian Digby

Answer 3

Hi Jan,

          As I mentioned in my original post, I realised that the workaround was simple, I was just confused by the fact that the AdvancedFilter interpreted the date differently when executed via VBA compared to when it was activated directly via the ribbon.

Once again, Thank you for your reply.