Sort Grouped Dates in Pivot Table

hwcarter11

New Member
Joined
Sep 19, 2006
Messages
7
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

hwcarter11

New Member
Joined
Sep 19, 2006
Messages
7
No ideas? It doesn't make sense that the month sorts correctly but the date (day) does not. Can anyone reproduce my issue?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,076
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 19, 2006
Messages
7
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
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
ABCD
3Sum of Amt
4MonthsDateTotal
5Jan3-Jan$ 13.44
62-Jan$ 6.72
724-Jan$ 9.96
822-Jan$ 13.10
91-Jan$ 11.29
1018-Jan$ 8.39
1112-Jan$ 10.55
1211-Jan$ 13.67
13Feb5-Feb$ 22.87
143-Feb$ 29.34
152-Feb$ 29.24
1622-Feb$ 8.97
1721-Feb$ 21.49
181-Feb$ 29.09
1912-Feb$ 8.27
2010-Feb$ 30.23
21Grand Total$ 266.62
Sheet12


WORKAROUND
book1.xls
ABCD
3Sum of Amt
4MonthDateTotal
52006 02Feb 22$ 8.97
6Feb 21$ 21.49
7Feb 12$ 8.27
8Feb 10$ 30.23
9Feb 05$ 22.87
10Feb 03$ 29.34
11Feb 02$ 29.24
12Feb 01$ 29.09
132006 02 Total$ 179.50
142006 01Jan 24$ 9.96
15Jan 22$ 13.10
16Jan 18$ 8.39
17Jan 12$ 10.55
18Jan 11$ 13.67
19Jan 03$ 13.44
20Jan 02$ 6.72
21Jan 01$ 11.29
222006 01 Total$ 87.12
23Grand Total$ 266.62
Sheet11
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

For what it's worth, I have no such problem, even when the dataes aren't sorted in the source table:
Book1
ABCDEF
1DateNumSum of Num
21/01/20011MonthsDateTotal
32/01/20012Jan1-Jan5
43/01/200132-Jan2
51/01/200143-Jan19
622/01/2001511-Jan11
73/01/2001612-Jan12
818/01/2001718-Jan7
922/01/2001822-Jan13
1024/01/2001924-Jan9
113/01/200110Grand Total78
1211/01/200111
1312/01/200112
Sheet1
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Nor do I have a problem (Excel 2000):
Book6
ABCDEF
1DateAmtSum of Amt
203/01/06$13.44MonthsDateTotal
302/01/06$6.72Feb22-Feb$8.97
424/01/06$9.9621-Feb$21.49
522/01/06$13.1012-Feb$8.27
601/01/06$11.2910-Feb$30.23
718/01/06$8.3905-Feb$22.87
812/01/06$10.5503-Feb$29.34
911/01/06$13.6702-Feb$29.24
1005/02/06$22.8701-Feb$29.09
1103/02/06$29.34Feb Total$179.50
1202/02/06$29.24Jan24-Jan$9.96
1322/02/06$8.9722-Jan$13.10
1421/02/06$21.4918-Jan$8.39
1501/02/06$29.0912-Jan$10.55
1612/02/06$8.2711-Jan$13.67
1710/02/06$30.2303-Jan$13.44
1802-Jan$6.72
1901-Jan$11.29
20Jan Total$87.12
21Grand Total$266.62
Sheet1
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Excel 2003 (SP2 11.8033.8036):
Book2
ABCDEF
1DateAmtSum of Amt
203/01/2006$13.44MonthsDateTotal
302/01/2006$6.72Feb22-Feb$8.97
424/01/2006$9.9621-Feb$21.49
522/01/2006$13.1012-Feb$8.27
601/01/2006$11.2910-Feb$30.23
718/01/2006$8.3905-Feb$22.87
812/01/2006$10.5503-Feb$29.34
911/01/2006$13.6702-Feb$29.24
1005/02/2006$22.8701-Feb$29.09
1103/02/2006$29.34Jan24-Jan$9.96
1202/02/2006$29.2422-Jan$13.10
1322/02/2006$8.9718-Jan$8.39
1421/02/2006$21.4912-Jan$10.55
1501/02/2006$29.0911-Jan$13.67
1612/02/2006$8.2703-Jan$13.44
1710/02/2006$30.2302-Jan$6.72
1801-Jan$11.29
19Grand Total$266.62
Sheet1
 

Forum statistics

Threads
1,141,625
Messages
5,707,476
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top