[excel] Ordering issue with date values when creating pivot tables

I have a 2-column spreadsheet which contains Dates and Sales figures as follows:

When doing Right click -> Format Cells the values in the Dates column properly appear as Date (in the M/D/YYYY format). This applies to all the Dates cells with the exception of the header (I checked with Ctrl+Shirt+Down).

However when I create a pivot table from the 2 columns, the Dates are recognized as text and are sorted accordingly (i.e. 1st sorted by month, then by day, then by year) which messes up my data:

I create pivot tables with this type of data on a regular basis and never had this issue before, and I really don't see what's wrong there.

How can I force the date values to be recognized as such when creating pivot tables?

PS: I have uploaded the pivot_table_date_porder_issue.xlsx file which exhibits the problem for whoever wants to see it.

Go into options. You most likely have 'Manual Sort" turned on. You need to go and change to radio button to "ascending > date". You can also right click the row/column, "more sorting options". It took me forever to find this solution...

April 20, 2017

I've read all the previously posted answers, and they require a lot of extra work. The quick and simple solution I have found is as follows:

1) Un-group the date field in the pivot table. 2) Go to the Pivot Field List UI. 3) Re-arrange your fields so that the Date field is listed FIRST in the ROWS section. 4) Under the Design menu, select Report Layout / Show in Tabular Form.

By default, Excel sorts by the first field in a pivot table. You may not want the Date field to be first, but it's a compromise that will save you time and much work.

You need to select the entire column where you have the dates, so click the "text to columns" button, and select delimited > uncheck all the boxes and go until you click the button finish.

This will make the cell format and then the values will be readed as date.

Hope it will helped.

Try creating a new pivot table, and not just refreshing.

I had a case where I forgot to add in a few dates. After adding them in I updated the pivot table range and hit refresh. They appeared at the end of the pivot table, out of order. I then tried to simply create a new pivot table and the dates where all in order.

The problem I had was that although the source data was correctly formatted as 'date' dd/mm/yyyy, the pivot table placed (for example) 22/05/2019 between 16/05/2019 and 17/05/2019. This data was visible in the pivot table, but in the wrong place. In addition, the Pivot chart refused to show that data for that date even though the 'Date' filter allowed it to be selected.

In my case, I had to:

1. From the Pivot Chart,open the 'Date' Filter menu.

2. select the 'Sort Oldest to Newest' option.

3. Bingo! The Pivot chart now shows the missing date data.

If you want to use a column with 24/11/15 (for 24th November 2015) in your Pivot that will sort correctly, you can make sure it is properly formatted by doing the following - highlight the column, go to Data – Text to Columns – click Next twice, then select “Date” and use the default of DMY (or select as applicable to your data) and click ok

When you pivot now you should see it sorting properly as we have properly formatted that column to be a date field so Excel can work with it

I saw this somewhere else. I am using 2016 Excel. What worked for me was to use YYYY Quarters (I was looking for quarterly data). So, I had the source data sorted as YYYY xQ. 2016 1Q, 2016 2Q, 2016 3Q, 2016, 4Q, 2017 1Q, 2017 2Q... You get the idea.