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

 

[hwcarter11]

No ideas? It doesn't make sense that the month sorts correctly but the date (day) does not. Can anyone reproduce my issue?

 

[Joe4]

Are the dates entered/formatted as Dates or Text?

It might be more helpful if you can post a sample of your data, using "Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board. " found at the bottom of the page, or by using Erik.Van.Geit's "Table-It" program.

 

[hwcarter11]

The data is formatted as date. I tried the posting app but it didn't want to work for some reason.

I am simply grouping the date data by month and day, sorting descending by date and it displays the months correctly but the day does not sort correctly.
<TABLE border="2">
<TD>
Sep
</TD>
<TD>
3-Sep
</TD>
<TD>
$1,000.00
</TD>
<TR>
<TD>

</TD>
<TD>
2-Sep
</TD>
<TD>
$1200.00
</TD>
</TR>
<TR>
<TD>

</TD>
<TD>
19-Sep
</TD>
<TD>
$1100.00
</TD>
</TR>
</TABLE>

 

[Greg Truby]

Grrrrr!!! This is just nuts!!! Yes, I am recreating your problem. Much to my chagrine. Worse, Excel is very neatly eluding everything I can think of to do this in the pivot. Still working on it... BRB (I hope)

 

[Greg Truby]

Well, I can't suss out how to get Excel to behave properly on this. For a workaround I created a help column Month with the formula =text(A2,"yyyy mm") and then put this left of the date field which I did not group on. If you don't group on the date then it sorts just fine. It only seems to disintegrate into nonsense when grouping.

DEFAULT

book1.xls
	A	B	C	D
3	Sum of Amt
4	Months	Date	Total
5	Jan	3-Jan	$ 13.44
6		2-Jan	$ 6.72
7		24-Jan	$ 9.96
8		22-Jan	$ 13.10
9		1-Jan	$ 11.29
10		18-Jan	$ 8.39
11		12-Jan	$ 10.55
12		11-Jan	$ 13.67
13	Feb	5-Feb	$ 22.87
14		3-Feb	$ 29.34
15		2-Feb	$ 29.24
16		22-Feb	$ 8.97
17		21-Feb	$ 21.49
18		1-Feb	$ 29.09
19		12-Feb	$ 8.27
20		10-Feb	$ 30.23
21	Grand Total		$ 266.62
Sheet12

WORKAROUND

book1.xls
	A	B	C	D
3	Sum of Amt
4	Month	Date	Total
5	2006 02	Feb 22	$ 8.97
6		Feb 21	$ 21.49
7		Feb 12	$ 8.27
8		Feb 10	$ 30.23
9		Feb 05	$ 22.87
10		Feb 03	$ 29.34
11		Feb 02	$ 29.24
12		Feb 01	$ 29.09
13	2006 02 Total		$ 179.50
14	2006 01	Jan 24	$ 9.96
15		Jan 22	$ 13.10
16		Jan 18	$ 8.39
17		Jan 12	$ 10.55
18		Jan 11	$ 13.67
19		Jan 03	$ 13.44
20		Jan 02	$ 6.72
21		Jan 01	$ 11.29
22	2006 01 Total		$ 87.12
23	Grand Total		$ 266.62
Sheet11

 

[PaddyD]

For what it's worth, I have no such problem, even when the dataes aren't sorted in the source table:

Book1
	A	B	C	D	E	F
1	Date	Num		Sum of Num
2	1/01/2001	1		Months	Date	Total
3	2/01/2001	2		Jan	1-Jan	5
4	3/01/2001	3			2-Jan	2
5	1/01/2001	4			3-Jan	19
6	22/01/2001	5			11-Jan	11
7	3/01/2001	6			12-Jan	12
8	18/01/2001	7			18-Jan	7
9	22/01/2001	8			22-Jan	13
10	24/01/2001	9			24-Jan	9
11	3/01/2001	10		Grand Total		78
12	11/01/2001	11
13	12/01/2001	12
Sheet1

 

[Andrew Poulsom]

Nor do I have a problem (Excel 2000):

Book6
	A	B	C	D	E	F
1	Date	Amt		Sum of Amt
2	03/01/06	$13.44		Months	Date	Total
3	02/01/06	$6.72		Feb	22-Feb	$8.97
4	24/01/06	$9.96			21-Feb	$21.49
5	22/01/06	$13.10			12-Feb	$8.27
6	01/01/06	$11.29			10-Feb	$30.23
7	18/01/06	$8.39			05-Feb	$22.87
8	12/01/06	$10.55			03-Feb	$29.34
9	11/01/06	$13.67			02-Feb	$29.24
10	05/02/06	$22.87			01-Feb	$29.09
11	03/02/06	$29.34		Feb Total		$179.50
12	02/02/06	$29.24		Jan	24-Jan	$9.96
13	22/02/06	$8.97			22-Jan	$13.10
14	21/02/06	$21.49			18-Jan	$8.39
15	01/02/06	$29.09			12-Jan	$10.55
16	12/02/06	$8.27			11-Jan	$13.67
17	10/02/06	$30.23			03-Jan	$13.44
18					02-Jan	$6.72
19					01-Jan	$11.29
20				Jan Total		$87.12
21				Grand Total		$266.62
Sheet1

 

[Greg Truby]

Hi Paddy. Can you try double-clicking on the DATE field's column heading, clicking the Advanced... button and then adjusting the AutoSort options to Descending? For me everything was just peaches 'til I did that. After doing that, the ruddy thing (a) kept sorting as strings and (b) would not let me format the field to anything other than d-mmm so I couldn't use something like a mm-dd format to overcome the sorting-as-strings snafu.

Andrew, if you are able to use Grouping and then AutoSort|Descending and get that kind of output, then XL2000 is behaving differently than [my] XL2003!

 

[Andrew Poulsom]

Excel 2003 (SP2 11.8033.8036):

Book2
	A	B	C	D	E	F
1	Date	Amt		Sum of Amt
2	03/01/2006	$13.44		Months	Date	Total
3	02/01/2006	$6.72		Feb	22-Feb	$8.97
4	24/01/2006	$9.96			21-Feb	$21.49
5	22/01/2006	$13.10			12-Feb	$8.27
6	01/01/2006	$11.29			10-Feb	$30.23
7	18/01/2006	$8.39			05-Feb	$22.87
8	12/01/2006	$10.55			03-Feb	$29.34
9	11/01/2006	$13.67			02-Feb	$29.24
10	05/02/2006	$22.87			01-Feb	$29.09
11	03/02/2006	$29.34		Jan	24-Jan	$9.96
12	02/02/2006	$29.24			22-Jan	$13.10
13	22/02/2006	$8.97			18-Jan	$8.39
14	21/02/2006	$21.49			12-Jan	$10.55
15	01/02/2006	$29.09			11-Jan	$13.67
16	12/02/2006	$8.27			03-Jan	$13.44
17	10/02/2006	$30.23			02-Jan	$6.72
18					01-Jan	$11.29
19				Grand Total		$266.62
Sheet1