Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting on PivotTable | Excel Worksheet Functions | |||
PivotTable Group by Week and Month | Excel Discussion (Misc queries) | |||
Week/month/year rollovers with PivotTable | Excel Worksheet Functions | |||
Sorting in PivotTable | Excel Discussion (Misc queries) | |||
PivotTable - Count by Month | Excel Worksheet Functions |