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. :(
Answer
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.
No comments:
Post a Comment