Sorting date ranges

[Jameo]

Hi all,

I have my raw data dates in the form dd/mm/yy hh:mm. The cells are formatted with the custom date format dd/mm/yy hh:mm.

A macro then copies are processes the raw data and places the dates in the form mmm-yy. Again the colums are formatted with the date format in the form mmm-yy.

When this data is plotted using a pivot chart however, the auto sort does not work. It sorts the dates as follows

Jan-11
Feb-11
Dec-10
Nov-10
Oct-10

I have ensured all date ranges are formatted as dates and not text and this problem is still occuring. At the moment i am just manually sorting my pivot tables, but I need to fix this issue with the autosort.

When I use the sort option for a column field it brings up a warning stating that some numbers are formatted as text. However, I have double and triple checked and every value in the column is formatted as a custom date and NOT text. This is obviously where the problem is coming in, but I have no idea how or why.

Does anyone have any ideas?

Thanks

 

[Weaver]

I've come up against this before and I suspect it's a 'feature' of pivot tables.

The way I got around it was to use the date format:

YYYY MM mmm

So the dates still get sorted correctly and you still get the jan, feb, mar portion at the end.

HTH

 

[Jameo]

Thanks for the input Weaver, unfortunately it doesn't seem to be working for me. I'll keep having a look around and report the fix if I find it.

Thanks