Ordering issue with date values when creating pivot tables

Question

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

User · Answer

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:

From the Pivot Chart,open the 'Date' Filter menu.
select the 'Sort Oldest to Newest' option.
Bingo! The Pivot chart now shows the missing date data.

User · Answer

Your problem is that excel does not recognize your text strings of  mm dd yyyy  as date objects in it s internal memory  Therefore when you create pivottable it doesn t consider these strings to be dates   You ll need to first convert your dates to actual date values before creating the pivottable  This is a good resource for that  http   office microsoft com en-us excel-help convert-dates-stored-as-text-to-dates-HP001162867 aspx  In your spreadsheet I created a second date column in B with the formula  DATEVALUE A2   Creating a pivot table with this new date column and Count of Sales then sorts correctly in the pivot table  option becomes Sort Oldest to Newest instead of Sort A to Z

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to date

Examples related to formatting

Examples related to pivot-table