Excel PivotTable Sorts Dates Wrong When Grouping by Date

I have a table with dates in Column A (and they are dates and recognized and stored in memory as dates by Excel)

Proof that Excel (2016) is recognizing them as dates, I tried converting the value to a date with a DATEVALUE() call, but it shows that the value is stored in memory as an Excel date:

This is the terrible sort that Excel is choosing when I attempt to sort by Oldest/Newest by right clicking on a "10-Jun" field:

Another issue/bug/side-effect is that when I attempt to change the format, it ignores my changes.

This issue is very odd and I'm not sure if I found a bug in Excel, however it's currently ruining my graphs. :(

6

8 Answers

I solved it. There must be some form of bug in Excel 2016 with Pivot Table date handling, but when I created the Pivot Table and checked the "Add this data to the Data Model" checkbox, Excel now sorts my dates correctly.

---

Update:

For those of you who are having issues with dates and managing other data in Excel, Microsoft's newest Office product Power BI is a great solution (that handles dates better) and lets you do transformations on your data.

4

When you do a pivot table the field chooser sometimes automatically changes your actual dates into "Months" or "Years". When your pivot table uses the "Month" value, it's not using the date value so it can't sort it. I just removed the "Month" from my Field Chooser (in my case the dates were the months the data was created in) and used the actual dates (which were the same for all values in a month.) This also fixes the formatting issue since it is now formatting a date, not the value "Jan".

1

This solution did not work for me. My solution is:

First, "Text to Columns" as;

Select the column of dates, Under DATA, select “Text to Columns”, Select Delimited – Next, Untick ALL delimiters – Next, Select column format “Date: MDY” – Finish.

Second, I changed the date format from "11/21/2017" to "General" then to "21-Nov-2017" And, I created a new Pivot Table (a new PT may not necessary).

I think Excel does not like xx/xx/xxxx format

Regards,

I've had this problem a number of times in various spreadsheets - The common theme is that the spreadsheet is an output sheet from another piece of software rather than being a spreadsheet that has been created manually from scratch. This seems to introduce something that prevents Excel from interpreting dates correctly.

My solution (which works for me) is this:

1. Highlight all the dates in the offending 'date' column on the data source sheet.
2. CTRL+X to cut the data.
3. Open a fresh Microsoft Word document and CTRL+V to paste the data in here.
4. Click elsewhere on the Word doc to unhighlight the pasted data.
5. Go back to your spreadsheet. Create a new column named something different e.g. 'datefixed'.
6. Back to the word doc, CTRL+A to select all then CTRL+C to copy the data.
7. Go back to the spreadsheet, select the top cell of your new column, right click and select Paste 'Keep source formatting'.
8. Highlight this new column. Right click and select 'format cells'.
9. Select 'date' category and *14/03/2001 in the Type field. OK out.

Create a new Pivot table based on this data and you should find that if you drag your new column name (i.e. 'datefixed') into the new Row label field it should be sorted chronologically.

(MSOffice Professional Plus 2010)

I followed the "Add this data to the Data Model" solution and started creating a duplicate pivot table that truly sorted the Date field correctly. As I was trying to duplicate all formats of the original I clicked on the sort button to sort the Date field and the problem appeared again. I double checked that the sorting was the culprit and then tried it on the original pivot table, that wasn't Added to the data model, but was sorted. And it worked.

So, instead of Ascending/Descending order do the following :

For a Data Model added pivot table : right click on a date > Sort > More Sort Options... > Data source order

For a NON Data Model added pivot table : right click on a date > Sort > More Sort Options... > Manual

Excel 365

Cheap and easy workaround is add hours to the Group by columns and collapse the entire hours field. It will sort and chart correctly by month/day.

In a blank column type =A2*1 and Excel will figure out that the date in A2 was not text, but is a number. Then Copy and Paste Special as 'Values' over the original date to convert this formula to a number.

Two solutions worked for me:

• The one by the OP ("Add this data to the Data Model" checkbox)
• Ungrouping the date field (if you don't need graphs, as the OP explained)
  • Problem: descending order is wrong (seems to sort by text instead of date):
    
  • Ungrouping:
    
  • Descending order is now fixed:
    
  • Source: comments below the OP answer + Microsoft Web site

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy