urgent pls! sort date pivot report

[Guest]

Have a pivot report with dates. I like to data sort by date in ascending
order. so i did sort and top 10 -> asending

but i keep getting dates in this order ->
11/07/2005
12/07/2005
13/06/2005
13/07/2005

but i would like to get in this order

11/07/2005
12/07/2005
13/07/2005
13/06/2005
How can i achieve this ???

 

[Guest]

Also, i have count and sum formulas in the report, does this effect sorting ?

 

[Max]

Try adding a new col, "Day" to the source table
with the formula: =DAY(A2), copied down
assuming "Date" col is col A

Then pivot it with "Day" above "Date" in the ROW area
(in step 3 of the wizard)

Set Subtotals for "Day" to None
For "Date", set it to Autosort > Descending
(in the PT Field Advanced Options dialog)

This yields the desired order for the "Date" col in the PT:

(Hide away the "Day" col, if required)

--

 

[Andrew Taylor]

Have a pivot report with dates. I like to data sort by date in ascending
order. so i did sort and top 10 -> asending

but i keep getting dates in this order ->
11/07/2005
12/07/2005
13/06/2005
13/07/2005

These are sorted lexicographcally, which suggests that the dates
must be in text format in the source data.

but i would like to get in this order

11/07/2005
12/07/2005
13/07/2005
13/06/2005

But this isn't ascending order - 13/06/2005 should come first.
I assume there's a typo in there somewhere (e.g. should be
13/06/2006 or 13/08/2005)

How can i achieve this ???

Either fix the source data so that you have genuine dates rather
than text, or add an extra column: you can convert a "text date"
to a real one by using the VALUE() function and formatting as
dd/mm/yyyy; then pivot on this column instead of the text dates.

 

[Guest]

Hi Thanks for the suggestions. Basically, the date doesn't get sorted
ascending or descending. And the source date is actually in date format.

While date is in actually A Col, B column contains No of sales and Sale
amount for each date. Hence two date cells are merged in the report. Is it
possible that this is causing the problem ?

Also, i didn't create the Pivot report and i dont know much about them. So i
can't do much with the source data and recreate another report.

 

[Max]

FWLIW, here's a sample construct (steps described in my response to you in
the other branch) which delivers exactly (imo) what you stated you wanted in
your orig. post:
http://www.savefile.com/files/6699697
SortByDayAscending_ByMonthDescending_PivotTable
_vbastarter_misc.xls