Pivottable, month, sorting
 

Hi,

On my worksheet, I have a number of invoicedates of this variety:
20031119, 20031126, 20031217 etc.

Using MID, I take the month part and SELECT CASE it to the 'name' of the
month, which I then write in another column on the same row. Prior to
that I had formatted that cell to contain "month only" dates.
It should be clear to Excel that my new column contains months.


Now I can use this 'month dimension' in my pivottable.



Problem is, when I drag the month dimension into the pivot's row area,
the months are sorted using their value, instead of their chronical
order. Data sorting (asc/desc) doesn't help.



thx

Ward

Pivottable, month, sorting
 

If you're using the full month names, or three letter abbreviations, the
months should sort in chronological order.

Or you could add a column in which you calculate the date from the
invoice date, e.g.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Add that column to the pivot table, and group by month.

Ward wrote:
Hi,

On my worksheet, I have a number of invoicedates of this variety:
20031119, 20031126, 20031217 etc.

Using MID, I take the month part and SELECT CASE it to the 'name' of the
month, which I then write in another column on the same row. Prior to
that I had formatted that cell to contain "month only" dates.
It should be clear to Excel that my new column contains months.


Now I can use this 'month dimension' in my pivottable.



Problem is, when I drag the month dimension into the pivot's row area,
the months are sorted using their value, instead of their chronical
order. Data sorting (asc/desc) doesn't help.



thx

Ward





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html