I'm having an issue in Excel 2002 when running a pivottable from date imported from Access 97.

The Access database has some calculated fields in a query which take a date and calculate the week commencing, i've enclosed the formula in the CDate command which ensures the date is formatted as a date.

The Excel spreadsheet imports the data from the database and updates when the file opens and then various pivottables run from this (a workaround to the fact that I produced the Access database in the middle of a project and don't have the time to change all of the reports that ran from the old spreadsheet which is now the database!).

The issue I have is that when in the spreadsheet the dates in the calculated w/c field are definately formatted as dates (I can run formulas on them etc), but when I create a pivottable using this field it sorts them alphabetically rather than in date order (i.e. 01/01/2007, 01/10/2006, 02/11/2006, 02/02/2007..etc), which is irritating at the least. I don't understand why excel is treating this column as a date column for all intents and purposes but as a text column for the pivottable.

I've created another field with the formula DATE(YEAR(BW10),MONTH(BW10),DAY(BW10) and then run the pivottable and sorted by this field and it works just fine, i'm exasperated with this issue as it would seem to be a glitch but I can't find a simple workaround.

Thanks in advance.

Your work-around is to format the date in the YYMMDD format, so as text or a date this will be sorted correctly.

Lee