Excel PivotTable Sorts Dates Wrong When Grouping by Date

Solution 1:

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.

Solution 2:

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".