I have some dates in the format mm/yyyy that are used in a pivot table.
The problem is the dates are 092005, 102005, 112005, 122005, 012006 & 022006.
Obviously when this goes in to the pivot table it gets sorted into order as a number so ends up like this:
012006, 022006, 092005, 102005, 112005, 122005
Is there a way of preventing this?
Thanks

Howdy. Not directly in Pivot. However, can you go back into the original data? IF so, suppose your current date is in cell L2, then in M2 put this formula:

=DATE(RIGHT(L2,4),LEFT(L2,2),MID(L2,3,2))

Copy down as far as your data, and in M1, put a heading (i.e. Date1). Then go to the Pivot wizard and change the data structure to include the next column. Now base you use the dates for Date1, rather than Date.

(Apologies for the delay, I'm only just catching up on some old posts. Maybe this helps.)

Preventing it, no. Getting the order you want, yes.

You can right click on the resultant pivot table entry and manually set the sort order (of individual entries). This will be no fun if you have thousands of entries.

You can select the pivot table field and do a normal sort on it - via data, sort. If you want a custom sort order, create that via Tools, Options, Custom Lists. So, to create your custom sort, you could have a list of possible results 092005, 102005, 112005, 122005, 012006 & 022006 in the order you want. Or, if in a different order, have a formula that allows you to sort on the results of the formula to get the order you want. Then put that new list as a custom sort order. You can do this via VBA if you want (Application.CustomList or similar) but I recollect it is best avoided.

HTH,
Fazza