Issuing an order with date values ​​when creating pivot tables

Question

I have 2 columns of a table that contains dates and sales numbers as follows:

When you right-click → Format Cells, the values ​​in the Dates column are correctly displayed as Date (in the format M/D/YYYY ). This applies to all Dates cells except for the title (I checked Ctrl + Shirt + Down).

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

I regularly create pivot tables with this data type, and I have never had this problem before, and I really don’t see what is wrong there.

How to make date values ​​be recognized as such when creating pivot tables?

PS: I downloaded the pivot_table_date_porder_issue.xlsx file which is a problem for those who want to see this.

Answer 1

Your problem is that excel does not recognize your "mm / dd / yyyy" text strings as date objects in internal memory. Therefore, when you create pivottable, they do not consider these lines as dates.

You must first convert your dates to actual date values ​​before creating a Pivottable. This is a good resource for this: http://office.microsoft.com/en-us/excel-help/convert-dates-stored-as-text-to-dates-HP001162867.aspx

In your table, I created a second date column in B using the formula =DATEVALUE(A2) . Creating a pivot table with this new date column and Count of Sales then sorted correctly in the pivot table (option becomes Sort Oldest to Newest instead of Sort A to Z ).

Answer 2

April 20, 2017

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

1) Disconnect the date field in the pivot table. 2) Go to the user interface of the summary field list. 3) Rebuild your fields so that the Date field is indicated by FIRST in the ROWS section. 4) From the Design menu, select Report Layout / Show in tabular form.

By default, Excel is sorted by the first field in the pivot table. You may not want the date field to be the first, but it is a compromise that will save you time and a lot of work.

Answer 3

If you want to use a column from 24/11/15 (for November 24, 2015) in your Pivot that will sort correctly, you can make sure that it is formatted correctly by doing the following: select the column, go to Data - Text to Columns - click "Next" twice, then select "Date" and use the default value DMY (or select that applies to your data) and click ok

Now that you are turning, you should see that it is sorted correctly, since we correctly formatted this column as a date field so that Excel can work with it.

Answer 4

Go into the options. Most likely you have "Manual Sort" enabled. You need to go and set the switch to "ascending> date". You can also right-click the “Advanced Sort Options” row / column. It took me a whole time to find this solution ...

Answer 5

Try creating a new pivot table rather than just updating.

I had a case when I forgot to add some dates. After adding them, I updated the pivot table range and updated the update. They appeared at the end of the pivot table, failed. Then I tried to create a new pivot table and dates when everything is in order.

Answer 6

You need to select the entire column in which you have dates, so click the "text to columns" button and select "Delimited"> uncheck all the boxes and go through until you click the "Finish" button.

This will make the cell format, and then the values ​​will be read as the date.

Hope this helps.

Answer 7

I saw it somewhere else. I am using 2016 excel. What helped me was the use of YYYY quarters (I was looking for quarterly data). So, the source data was sorted as YYYY xQ. 2016 1st quarter of 2016 2nd quarter of 2016 3rd quarter of 2016, 4th quarter of 2017 1st quarter of 2017 2nd quarter ... You understand.

Answer 8

The problem I ran into was that, although the source data was correctly formatted as the “date” dd / mm / yyyy, the pivot table was posted (for example) on 05/22/2019 from 05/16/2019 to 05/17 05/2019. This data was visible in the pivot table, but in the wrong place. In addition, the pivot chart refused to show this data for this date, although the Date filter allowed it to be selected.

In my case, I had to:

1. In the pivot chart, open the Date filter menu.

2. select the "Sort from oldest to newest" option.

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