Ordering issue with date values when creating pivot tables
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
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).