sorting dates in a pivot table

[Mike]

I have a pivot table that toggles markets and products with metrics as row
labels and weeks as column labels. I would like the dates to be sorted from
oldest to most recent but cannot find any ways to do this. I have the
previous 52 weeks ending on 10/24/09 and the first week (when I tell Excel to
sort ascending by weeks is 1/10/09 and then there are some 2008 weeks a few
columns in. I've also tried to change the format of both the data set and
the dates in the pivot table but to no avail. Any thoughts?

Thanks in advance for your help with this.

 

[Dave Peterson]

If you change the format of the cell and the display doesn't change, then your
values aren't really dates--they're just text masquerading as dates.

I'd convert the dates in the table and then refresh the pivottable.

One easy way to change text dates to date dates is
select the column
data|text to columns (xl2003 menus)
Fixed width (but remove any lines that excel guessed)
choose date (mdy for the order)
and plop back where you found them.

Then give this range a nice unambiguous date format so that it'll be easy to
check:

mmmm dd, yyyy

If any cells don't change the display, then they didn't get converted correctly.

In fact, if you had cells that did change format before this correction, then
I'm betting your data was originally some sort of text. And it was imported to
excel.

The bad news is that the cells that contain actual dates may not be correct.
I'd go back to the original source and see if you had dates that looked like:

01/02/03
and if the equivalent field in excel is whatever that original date was supposed
to be (Jan 02, 2003 or feb 01, 2003 or whatever...)