Forum Discussion
Date Format: Pivot Table vs Source Data
- SergeiBaklanNov 16, 2023MVP
Perhaps you have texts which looks like date, not dates which are actually (in behind) numbers.
- OrinWhitener2787Jan 31, 2024Copper Contributor
SergeiBaklan What if one of the things being sorted on is an aggregated group name abbreviation that comes out to something like "JAN" and the pivot table seems to be auto interpreting it as "January" despite all data types being set appropriately as Text and non-pivot tables with the same Data Type settings are sorting alphabetically appropriately?
example:
- Column Header "Group Name".
- All "Group Name" cells set as Text data type.
- In Pivot Table all "Group Name" column cells set as Text data type.
- Multiple rows with "Group Name" values of "JAN" and "AST".
- On source data sorting by "Group Name" sorts as:
- AST
- JAN
- On pivot table sorting by "Group Name" sorts as:
- JAN
- AST
Some Screenshots below:
Data Source unsorted:
Data Source Sorted:Pivot Table Sorted:
Is something like this just an extremely native limitation of current Excel and its ability to respect Data Types when creating a pivot table with source data at the moment? Doesn't seem like there are many resources specifically about this sorting issue and seems like you can't edit the default 3 letter sort list for the months in Excel's advanced options.
- SergeiBaklanJan 31, 2024MVP
In PivotTable
More Sort Options->More Options->disable Sort Automatically->choose No Calculations
- Column Header "Group Name".
- karri35Mar 27, 2024Copper Contributor
office1460 hii is it working date formatting