Sort Grouped Dates in Pivot Table

[hwcarter11]

I am grouping dates in a pivot table by month and day so users can easily select various ranges, but when I do this the day portion of the date is formatted dd-mmm and sorts incorrectly. I am trying to sort descending on the date field. Any suggestions?

Here is my output. Although I can't seem to get it formatted correctly in the post, I think you can see what it's doing.

Sum of Amount DOW
Months Date Monday Tuesday
Sep 9-Sep
7-Sep
6-Sep
5-Sep $3,407.05
1-Sep
14-Sep
13-Sep
12-Sep $2,337.73
11-Sep $3,637.47
Aug 9-Aug
8-Aug $10,880.04
7-Aug $8,499.01
4-Aug
3-Aug
31-Aug
30-Aug
2-Aug

 

[Greg Truby]

That's fascinating Andrew! I can't even get the formatting to change. My SP2 ref is (11.8012.6568). I thought all our machines were configured to pick up all Office 2003 updates, but now I'm wondering...

 

[PaddyD]

still no issue:

Book3
	A	B	C	D	E	F	G
1	Date	num		Sum of num
2	1/01/2001	1		Months	Date	Total
3	2/01/2001	2		Jan	5-Jan	4
4	1/01/2001	3			4-Jan	5
5	5/01/2001	4			2-Jan	2
6	4/01/2001	5			1-Jan	4
7	1/02/2001	6		Feb	5-Feb	7
8	5/02/2001	7			1-Feb	14
9	1/02/2001	8		Grand Total		36
10
Sheet1

...still no issue on 2003 SP2 11.8033.2028

 

[Greg Truby]

Paddy, would the dates you have there generate the error? In addition to the ones you show, you'd need a couple in the teen and twenties to test. Andrew's results though make me think you're going to come out okay. Right now I am installing all of the MS updates that the MS Update site indicated that I was short. None however appears to be a general SP2 for Office 2003? So I can't figure out why you two's SP ref numbers are greater than mine. I'm plumb confuddled (not that this is uncommon ).

 

[PaddyD]

"Paddy, would the dates you have there generate the error?"

Good point. With the extra data elements, I can now re-create your problem. Equally, I cannot solve it.

It seems the autosort is pathelogically picking up the format, not the value - can be confirmed by trying to do a manual sort on the data - it's defaulting to labels as the key??

Hhmmm....