Pivot table sorting issue

[markster]

Hi

I have a pivotable sorting issue whereby columns mostly sort in date order and then randomly don't. Here's what it does

So you can see that the latter part of May is in the right order but then it goes up to 31-May is fine then it randomly goes to 7-May, 9-May, then 1-Jun, 2-Jun, 4 Jun.

I was thinking it must be something to do with the date format in the pivot table data so I tried to use the Datevalue function to convert the date (i.e Datevalue(A7) but this function just returns VALUE! error.

The actual data date is in the following format so seems like a date

Any ideas anyone?

Thanks
Mark

 

[arthurbr]

The actual data date is in the following format so seems like a date

Are the dates right or left aligned when you don't apply manual alignment? If left aligned : text. Apply one of the well known techniques to make them real dates

 

[markster]

Hi there - the dates align to the right so that's not the problem but this is happening with numbers too. Please see below. I just want everything to be ordered by date /numerical but I can't figure out why this is happening. Any other ideas? Cheers.

Thanks Mark

 

[markster]

Just to clarify the above is two different headings from 2 different pivot tables

 

[Alex Blakenburg]

I have to ask. Have you actually applied a sort ?
A pivot table appears to sort when you first create it but unless you actually apply a sort it will not sort when you refresh the data.

What is not visible from your screenshots is the heading showing the little sort icon which in the both the below is pointing up for ascending.

Tabular layout and Compact layout respectively

 

[markster]

oops sorry just getting to grips with pivot tables - i'm glad you mentioned that because I didn't and I have now and it's fine. I just assumed that they would automatically sort in order - I've learned something ! Thanks again.

 

[Alex Blakenburg]

No problem. Glad we could help.