I’ve been working on a macro-enabled pivot table dashboard in Excel. Everything has gone swimmingly, and I’ve even learned the rudiments of VBA, except for one very annoying problem with date formats.
Each row that goes into the pivot table has a date. The source file for the pivot table has correctly-formatted dates (format style m/d/yyyy, in VBA language). When the records are pulled into the table, they appear to be formatted correctly: if I try to format them manually, they’re displayed in date format. I can change the date formatting manually (say, from m/d/yyyy to dd/mm/yyyy), and the dates display with the correct format. If I move the date column to the extreme left, I can sort them, and 1/1/2009 will correctly be sorted after 9/1/2008.
The problem comes when I try to filter the dates. If I try to filter them manually using the Label Filter function in pivot tables, Excel treats the dates as text strings. The same is true if I try to write a VBA macro to filter the dates: filtering for dates between 5/1/2009 and 6/1/2009 brings up all dates between 5/1 and 6/1 in all years, and 12/31/2008 precedes 9/1/2008, etc. Even when I tried to trick Excel by formatting to yyyy/mm/dd (2009/01/01 would presumably precede 2008/12/31 in a text string), Excel wouldn’t bite.
Argh! After all I learned in VBA, this date format problem is bringing me down. Any help out there?