1

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:

Proof of Date Recognition

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:

Excel Incorrect Sort

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

Choosing Date Format Example

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
  • Did you try to convert your column using the Text to Date button?
    – Scorpion99
    Sep 28 '16 at 20:16
  • @Scorpion99 There's nothing to convert. Even after trying it still doesn't work properly, and I've also shown very clearly that it's already being stored as a date in memory already. Sep 28 '16 at 20:21
  • Absolutely not. The purpose of StackOverflow/SU is to share answers and solutions for the rest of the world to see. Sep 28 '16 at 20:25
  • Did you try to copy your data to another new excel file?
    – Scorpion99
    Sep 28 '16 at 20:26
  • @Scorpion99 The same issue happens Sep 28 '16 at 20:28
3

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.

enter image description here


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
  • That is REALLY strange! Thanks for closing the loop on this.
    – fixer1234
    Sep 28 '16 at 20:58
  • 1
    I have run across Pivot Tables changing dates to text strings (in previous versions of Excel), when grouping, in the past. I am glad to know about the Data Model fix. Sep 29 '16 at 1:14
  • @RonRosenfeld I suspected that it might be related to grouping too, because when I turned it off to only the "Date" field, it worked properly. However I did need grouping for my graphs :) Sep 29 '16 at 16:39
  • zomg, thanks so much. It treated it as a date in every single other respect, filtering, grouping, display ... it just wouldn't sort in the pivot table. It also was defaulting to count instead of sum for numbers in the pivot. Other searches had me going in circles with formatting issues. Is there a way to check the data model box after a pivot has already been created? Sep 28 '18 at 19:13
1

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
  • Do you mean the "Group by Item" for the Date field in the field chooser? Because that's not the issue here, Excel refuses to recognize the field as a date in the PivotTable. Mar 30 '18 at 16:44
0

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,

0

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)

0

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

0

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.

0

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.