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.

Answer 1

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.