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 ???

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

"vbastarter" wrote:

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 ???

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:

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

(Hide away the "Day" col, if required)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"vbastarter" wrote in message
...
Also, i have count and sum formulas in the report, does this effect
sorting ?

"vbastarter" wrote:

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 ???

vbastarter wrote:
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.

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.
"Andrew Taylor" wrote:


vbastarter wrote:
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.

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--