Excel problem: Pivot tables, date formats, and filter

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?

Problem solved, so I’ll add to the growing Excel knowledge base by describing the fix here. One (out of the 54,000+) date fields had a blank space instead of “no data.” That one blank space convinced Excel that the column was supposed to be formatted as a text string. Removing the blank space field fixed the issue.

the old "hidden text space"land mine. More hair has been extracted because of this than any other Excel problem. If this is a random event from a data upload you may try creating another column that multiplies the date column by 1 to force everthing into a value.

Yes, it is from a data upload (actually a download from a misbehaving database to a source file, then reloaded to a pivot table). Through the source file I should have a way of introducing a dummy date…good old 1/0/1900 to the rescue.