I formatted the date column in the source data how I wanted it, which is month/ year (for example May-2015 or Jun-2022). When I created a pivot table from this data, it reformatted the date to mm/d...
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.
Awesome, thank you for such a prompt reply. That was exactly what I needed.
Resources
"}},"componentScriptGroups({\"componentId\":\"custom.widget.MicrosoftFooter\"})":{"__typename":"ComponentScriptGroups","scriptGroups":{"__typename":"ComponentScriptGroupsDefinition","afterInteractive":{"__typename":"PageScriptGroupDefinition","group":"AFTER_INTERACTIVE","scriptIds":[]},"lazyOnLoad":{"__typename":"PageScriptGroupDefinition","group":"LAZY_ON_LOAD","scriptIds":[]}},"componentScripts":[]},"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/community/NavbarDropdownToggle\"]})":[{"__ref":"CachedAsset:text:en_US-components/community/NavbarDropdownToggle-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/common/QueryHandler\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/common/QueryHandler-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/EscalatedMessageBanner\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/EscalatedMessageBanner-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/users/UserLink\"]})":[{"__ref":"CachedAsset:text:en_US-components/users/UserLink-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserRank\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserRank-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageTime\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageTime-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSolvedBadge\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSolvedBadge-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSubject\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSubject-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageBody\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageBody-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageCustomFields\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageCustomFields-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageReplyButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageReplyButton-1745505309806"}],"message({\"id\":\"message:3780198\"})":{"__ref":"ForumTopicMessage:message:3780198"},"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserAvatar\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/ranks/UserRankLabel\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/AcceptedSolutionButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309806"}],"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/LinearReplyList\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/LinearReplyList-1745505309806"}],"message({\"id\":\"message:4044041\"})":{"__ref":"ForumReplyMessage:message:4044041"},"cachedText({\"lastModified\":\"1745505309806\",\"locale\":\"en-US\",\"namespaces\":[\"components/tags/TagView/TagViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309806"}],"message({\"id\":\"message:4045852\"})":{"__ref":"ForumReplyMessage:message:4045852"},"message({\"id\":\"message:4044998\"})":{"__ref":"ForumReplyMessage:message:4044998"}},"CachedAsset:pages-1745487429407":{"__typename":"CachedAsset","id":"pages-1745487429407","value":[{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"BlogViewAllPostsPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId/all-posts/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"CasePortalPage","type":"CASE_PORTAL","urlPath":"/caseportal","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"CreateGroupHubPage","type":"GROUP_HUB","urlPath":"/groups/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"CaseViewPage","type":"CASE_DETAILS","urlPath":"/case/:caseId/:caseNumber","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"InboxPage","type":"COMMUNITY","urlPath":"/inbox","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"HelpFAQPage","type":"COMMUNITY","urlPath":"/help","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"IdeaMessagePage","type":"IDEA_POST","urlPath":"/idea/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"IdeaViewAllIdeasPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/all-ideas/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"LoginPage","type":"USER","urlPath":"/signin","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"BlogPostPage","type":"BLOG","urlPath":"/category/:categoryId/blogs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"UserBlogPermissions.Page","type":"COMMUNITY","urlPath":"/c/user-blog-permissions/page","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ThemeEditorPage","type":"COMMUNITY","urlPath":"/designer/themes","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"TkbViewAllArticlesPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId/all-articles/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730142000000,"localOverride":null,"page":{"id":"AllEvents","type":"CUSTOM","urlPath":"/Events","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"OccasionEditPage","type":"EVENT","urlPath":"/event/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"OAuthAuthorizationAllowPage","type":"USER","urlPath":"/auth/authorize/allow","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"PageEditorPage","type":"COMMUNITY","urlPath":"/designer/pages","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"PostPage","type":"COMMUNITY","urlPath":"/category/:categoryId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ForumBoardPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"TkbBoardPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"EventPostPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"UserBadgesPage","type":"COMMUNITY","urlPath":"/users/:login/:userId/badges","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"GroupHubMembershipAction","type":"GROUP_HUB","urlPath":"/membership/join/:nodeId/:membershipType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"MaintenancePage","type":"COMMUNITY","urlPath":"/maintenance","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"IdeaReplyPage","type":"IDEA_REPLY","urlPath":"/idea/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"UserSettingsPage","type":"USER","urlPath":"/mysettings/:userSettingsTab","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"GroupHubsPage","type":"GROUP_HUB","urlPath":"/groups","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ForumPostPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"OccasionRsvpActionPage","type":"OCCASION","urlPath":"/event/:boardId/:messageSubject/:messageId/rsvp/:responseType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"VerifyUserEmailPage","type":"USER","urlPath":"/verifyemail/:userId/:verifyEmailToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"AllOccasionsPage","type":"OCCASION","urlPath":"/category/:categoryId/events/:boardId/all-events/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"EventBoardPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"TkbReplyPage","type":"TKB_REPLY","urlPath":"/kb/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"IdeaBoardPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"CommunityGuideLinesPage","type":"COMMUNITY","urlPath":"/communityguidelines","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"CaseCreatePage","type":"SALESFORCE_CASE_CREATION","urlPath":"/caseportal/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"TkbEditPage","type":"TKB","urlPath":"/kb/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ForgotPasswordPage","type":"USER","urlPath":"/forgotpassword","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"IdeaEditPage","type":"IDEA","urlPath":"/idea/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"TagPage","type":"COMMUNITY","urlPath":"/tag/:tagName","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"BlogBoardPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"OccasionMessagePage","type":"OCCASION_TOPIC","urlPath":"/event/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ManageContentPage","type":"COMMUNITY","urlPath":"/managecontent","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ClosedMembershipNodeNonMembersPage","type":"GROUP_HUB","urlPath":"/closedgroup/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"CommunityPage","type":"COMMUNITY","urlPath":"/","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ForumMessagePage","type":"FORUM_TOPIC","urlPath":"/discussions/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"IdeaPostPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730142000000,"localOverride":null,"page":{"id":"CommunityHub.Page","type":"CUSTOM","urlPath":"/Directory","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"BlogMessagePage","type":"BLOG_ARTICLE","urlPath":"/blog/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"RegistrationPage","type":"USER","urlPath":"/register","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"EditGroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ForumEditPage","type":"FORUM","urlPath":"/discussions/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ResetPasswordPage","type":"USER","urlPath":"/resetpassword/:userId/:resetPasswordToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730142000000,"localOverride":null,"page":{"id":"AllBlogs.Page","type":"CUSTOM","urlPath":"/blogs","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"TkbMessagePage","type":"TKB_ARTICLE","urlPath":"/kb/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"BlogEditPage","type":"BLOG","urlPath":"/blog/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ManageUsersPage","type":"USER","urlPath":"/users/manage/:tab?/:manageUsersTab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ForumReplyPage","type":"FORUM_REPLY","urlPath":"/discussions/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"PrivacyPolicyPage","type":"COMMUNITY","urlPath":"/privacypolicy","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"NotificationPage","type":"COMMUNITY","urlPath":"/notifications","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"UserPage","type":"USER","urlPath":"/users/:login/:userId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"OccasionReplyPage","type":"OCCASION_REPLY","urlPath":"/event/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ManageMembersPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/manage/:tab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"SearchResultsPage","type":"COMMUNITY","urlPath":"/search","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"BlogReplyPage","type":"BLOG_REPLY","urlPath":"/blog/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"GroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"TermsOfServicePage","type":"COMMUNITY","urlPath":"/termsofservice","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"CategoryPage","type":"CATEGORY","urlPath":"/category/:categoryId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"ForumViewAllTopicsPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/all-topics/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"TkbPostPage","type":"TKB","urlPath":"/category/:categoryId/kbs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745487429407,"localOverride":null,"page":{"id":"GroupHubPostPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"}],"localOverride":false},"CachedAsset:text:en_US-components/context/AppContext/AppContextProvider-0":{"__typename":"CachedAsset","id":"text:en_US-components/context/AppContext/AppContextProvider-0","value":{"noCommunity":"Cannot find community","noUser":"Cannot find current user","noNode":"Cannot find node with id {nodeId}","noMessage":"Cannot find message with id {messageId}"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/common/Loading/LoadingDot-0":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/common/Loading/LoadingDot-0","value":{"title":"Loading..."},"localOverride":false},"User:user:-1":{"__typename":"User","id":"user:-1","uid":-1,"login":"Deleted","email":"","avatar":null,"rank":null,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":"ANONYMOUS","registrationTime":null,"confirmEmailStatus":false,"registrationAccessLevel":"VIEW","ssoRegistrationFields":[]},"ssoId":null,"profileSettings":{"__typename":"ProfileSettings","dateDisplayStyle":{"__typename":"InheritableStringSettingWithPossibleValues","key":"layout.friendly_dates_enabled","value":"false","localValue":"true","possibleValues":["true","false"]},"dateDisplayFormat":{"__typename":"InheritableStringSetting","key":"layout.format_pattern_date","value":"MMM dd yyyy","localValue":"MM-dd-yyyy"},"language":{"__typename":"InheritableStringSettingWithPossibleValues","key":"profile.language","value":"en-US","localValue":"en","possibleValues":["en-US"]}},"deleted":false},"Theme:customTheme1":{"__typename":"Theme","id":"customTheme1"},"Category:category:microsoft365":{"__typename":"Category","id":"category:microsoft365","entityType":"CATEGORY","displayId":"microsoft365","nodeType":"category","depth":3,"title":"Microsoft 365","shortTitle":"Microsoft 365","parent":{"__ref":"Category:category:products-services"},"categoryPolicies":{"__typename":"CategoryPolicies","canReadNode":{"__typename":"PolicyResult","failureReason":null}}},"Category:category:top":{"__typename":"Category","id":"category:top","displayId":"top","nodeType":"category","depth":0,"title":"Top","entityType":"CATEGORY","shortTitle":"Top"},"Category:category:communities":{"__typename":"Category","id":"category:communities","displayId":"communities","nodeType":"category","depth":1,"parent":{"__ref":"Category:category:top"},"title":"Communities","entityType":"CATEGORY","shortTitle":"Communities"},"Category:category:products-services":{"__typename":"Category","id":"category:products-services","displayId":"products-services","nodeType":"category","depth":2,"parent":{"__ref":"Category:category:communities"},"title":"Products","entityType":"CATEGORY","shortTitle":"Products"},"Forum:board:ExcelGeneral":{"__typename":"Forum","id":"board:ExcelGeneral","entityType":"FORUM","displayId":"ExcelGeneral","nodeType":"board","depth":4,"conversationStyle":"FORUM","title":"Excel","description":"Your community for how-to discussions and sharing best practices on Microsoft Excel. If you’re looking for technical support, please visit Microsoft Support Community.","avatar":null,"profileSettings":{"__typename":"ProfileSettings","language":null},"parent":{"__ref":"Category:category:microsoft365"},"ancestors":{"__typename":"CoreNodeConnection","edges":[{"__typename":"CoreNodeEdge","node":{"__ref":"Community:community:gxcuf89792"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:communities"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:products-services"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:microsoft365"}}]},"userContext":{"__typename":"NodeUserContext","canAddAttachments":false,"canUpdateNode":false,"canPostMessages":false,"isSubscribed":false},"boardPolicies":{"__typename":"BoardPolicies","canPublishArticleOnCreate":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","key":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","args":[]}}},"shortTitle":"Excel","repliesProperties":{"__typename":"RepliesProperties","sortOrder":"REVERSE_PUBLISH_TIME","repliesFormat":"threaded"},"tagProperties":{"__typename":"TagNodeProperties","tagsEnabled":{"__typename":"PolicyResult","failureReason":null}},"requireTags":true,"tagType":"PRESET_ONLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/"},"Rank:rank:37":{"__typename":"Rank","id":"rank:37","position":18,"name":"Copper Contributor","color":"333333","icon":null,"rankStyle":"TEXT"},"User:user:1797910":{"__typename":"User","id":"user:1797910","uid":1797910,"login":"Ross321","deleted":false,"avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-9.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"email":"","messagesCount":3,"biography":null,"topicsCount":1,"kudosReceivedCount":1,"kudosGivenCount":0,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2023-03-27T12:40:42.517-07:00","confirmEmailStatus":null},"followersCount":null,"solutionsCount":0},"ForumTopicMessage:message:3780198":{"__typename":"ForumTopicMessage","uid":3780198,"subject":"Date Format: Pivot Table vs Source Data","id":"message:3780198","revisionNum":2,"repliesCount":12,"author":{"__ref":"User:user:1797910"},"depth":0,"hasGivenKudo":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3780198"},"readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3780198"},"body@stripHtml({\"truncateLength\":200})":" I formatted the date column in the source data how I wanted it, which is month/ year (for example May-2015 or Jun-2022). When I created a pivot table from this data, it reformatted the date to mm/d...","body@stringLength":"642","rawBody":"
I formatted the date column in the source data how I wanted it, which is month/ year (for example May-2015 or Jun-2022).
When I created a pivot table from this data, it reformatted the date to mm/dd/yyyy (for example 4/7/2014).
Question: how do I get the date format in the pivot table to match the source data? Or alternatively, how can I change the date format in the pivot table?
I need to shorten the date format, it's taking up too much room to display properly.
Using Office 365 for Enterprise on Windows 10 Pro
thanks,
Ross
","kudosSumWeight":0,"postTime":"2023-03-27T12:47:20.486-07:00","images":{"__typename":"AssociatedImageConnection","edges":[],"totalCount":0,"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"attachments":{"__typename":"AttachmentConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[]},"tags":{"__typename":"TagConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[{"__typename":"TagEdge","cursor":"MjUuMXwyLjF8b3wxMHxfTlZffDE","node":{"__typename":"Tag","id":"tag:excel","text":"excel","time":"2016-06-21T14:08:51.129-07:00","lastActivityTime":null,"messagesCount":null,"followersCount":null}},{"__typename":"TagEdge","cursor":"MjUuMXwyLjF8b3wxMHxfTlZffDI","node":{"__typename":"Tag","id":"tag:office 365","text":"office 365","time":"2016-06-27T01:27:02.990-07:00","lastActivityTime":null,"messagesCount":null,"followersCount":null}}]},"timeToRead":1,"currentRevision":{"__ref":"Revision:revision:3780198_2"},"latestVersion":null,"metrics":{"__typename":"MessageMetrics","views":127040},"visibilityScope":"PUBLIC","canonicalUrl":null,"seoTitle":null,"seoDescription":null,"isEscalated":null,"placeholder":false,"originalMessageForPlaceholder":null,"messagePolicies":{"__typename":"MessagePolicies","canModerateSpamMessage":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.feature.moderation_spam.action.moderate_entity.allowed.accessDenied","key":"error.lithium.policies.feature.moderation_spam.action.moderate_entity.allowed.accessDenied","args":[]}}},"archivalData":null,"customFields":[]},"Conversation:conversation:3780198":{"__typename":"Conversation","id":"conversation:3780198","solved":false,"topic":{"__ref":"ForumTopicMessage:message:3780198"},"lastPostingActivityTime":"2025-02-24T07:53:19.165-08:00","lastPostTime":"2025-02-24T07:53:19.165-08:00","unreadReplyCount":12,"isSubscribed":false},"ModerationData:moderation_data:3780198":{"__typename":"ModerationData","id":"moderation_data:3780198","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"Revision:revision:3780198_2":{"__typename":"Revision","id":"revision:3780198_2","lastEditTime":"2023-03-27T12:48:39.796-07:00"},"ForumReplyMessage:message:4044414":{"__typename":"ForumReplyMessage","id":"message:4044414","conversation":{"__ref":"Conversation:conversation:3780198"},"author":{"__ref":"User:user:521"},"revisionNum":1,"uid":4044414,"depth":7,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"subject":"Re: Date Format: Pivot Table vs Source Data","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4044414"},"parent":{"__ref":"ForumReplyMessage:message:4044041"},"body":"
","author":{"__ref":"User:user:521"},"isEscalated":null,"postTime":"2023-03-28T14:17:41.074-07:00"},"ForumReplyMessage:message:3983985":{"__typename":"ForumReplyMessage","id":"message:3983985","revisionNum":1,"uid":3983985,"depth":4,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3780198"},"subject":"Re: Date Format: Pivot Table vs Source Data","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3983985"},"body":"
SergeiBaklan what if you dont have that number format under the field settings
","body@stringLength":"782","rawBody":"
what if you dont have that number format under the field settings
","author":{"__ref":"User:user:2146207"},"isEscalated":null,"postTime":"2023-11-15T10:56:18.493-08:00","parent":{"__ref":"ForumReplyMessage:message:3781555"}},"ForumReplyMessage:message:3985016":{"__typename":"ForumReplyMessage","id":"message:3985016","revisionNum":1,"uid":3985016,"depth":5,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3780198"},"subject":"Re: Date Format: Pivot Table vs Source Data","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3985016"},"body":"
Perhaps you have texts which looks like date, not dates which are actually (in behind) numbers.
","body@stringLength":"318","rawBody":"
\n
Perhaps you have texts which looks like date, not dates which are actually (in behind) numbers.
","author":{"__ref":"User:user:521"},"isEscalated":null,"postTime":"2023-11-16T08:13:44.941-08:00","parent":{"__ref":"ForumReplyMessage:message:3983985"}},"ForumReplyMessage:message:4044041":{"__typename":"ForumReplyMessage","id":"message:4044041","revisionNum":2,"uid":4044041,"depth":6,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3780198"},"subject":"Re: Date Format: Pivot Table vs Source Data","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4044041"},"body":"
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.
","body@stringLength":"2903","rawBody":"
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.
","author":{"__ref":"User:user:2283996"},"isEscalated":null,"postTime":"2024-01-30T17:05:21.316-08:00","parent":{"__ref":"ForumReplyMessage:message:3985016"},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"AssociatedImage:{\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS00MDQ0NDE0LTU0NzU4NmlDRDU3NjE3MTQ0QkRGRUZC?revision=1\"}":{"__typename":"AssociatedImage","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/images/bS00MDQ0NDE0LTU0NzU4NmlDRDU3NjE3MTQ0QkRGRUZC?revision=1","title":"image.png","associationType":"BODY","width":417,"height":370,"altText":null},"Revision:revision:4044414_1":{"__typename":"Revision","id":"revision:4044414_1","lastEditTime":"2024-01-31T03:37:58.245-08:00"},"QueryVariables:ReplyList:message:4044414:1":{"__typename":"QueryVariables","id":"ReplyList:message:4044414:1","value":{"id":"message:4044414","sorts":{"postTime":{"direction":"DESC"}},"useAvatar":true,"useAuthorLogin":true,"useAuthorRank":true,"useBody":true,"useKudosCount":true,"useTimeToRead":false,"useMedia":false,"useReadOnlyIcon":false,"useRepliesCount":false,"useSearchSnippet":false,"useAcceptedSolutionButton":true,"useSolvedBadge":false,"useAttachments":false,"attachmentsFirst":5,"useTags":false,"useNodeAncestors":false,"useUserHoverCard":false,"useNodeHoverCard":false,"useModerationStatus":true,"usePreviewSubjectModal":false,"useMessageStatus":true}},"ROOT_MUTATION":{"__typename":"Mutation"},"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309806":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/users/UserAvatar-1745505309806","value":{"altText":"{login}'s avatar","altTextGeneric":"User's avatar"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309806":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/ranks/UserRankLabel-1745505309806","value":{"altTitle":"Icon for {rankName} rank"},"localOverride":false},"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309806":{"__typename":"CachedAsset","id":"text:en_US-components/messages/AcceptedSolutionButton-1745505309806","value":{"accept":"Mark as Solution","accepted":"Marked as Solution","errorHeader":"Error!","errorAdd":"There was an error marking as solution.","errorRemove":"There was an error unmarking as solution.","solved":"Solved"},"localOverride":false},"CachedAsset:text:en_US-components/messages/LinearReplyList-1745505309806":{"__typename":"CachedAsset","id":"text:en_US-components/messages/LinearReplyList-1745505309806","value":{"title":"{count, plural, one{# Reply} other{# Replies}}","title@board:BLOG":"{count, plural, one{# Comment} other{# Comments}}","title@board:TKB":"{count, plural, one{# Comment} other{# Comments}}","title@board:IDEA":"{count, plural, one{# Comment} other{# Comments}}","title@board:OCCASION":"{count, plural, one{# Comment} other{# Comments}}","noRepliesTitle":"No Replies","noRepliesTitle@board:BLOG":"No Comments","noRepliesTitle@board:TKB":"No Comments","noRepliesTitle@board:IDEA":"No Comments","noRepliesTitle@board:OCCASION":"No Comments","noRepliesDescription":"Be the first to reply","noRepliesDescription@board:BLOG":"Be the first to comment","noRepliesDescription@board:TKB":"Be the first to comment","noRepliesDescription@board:OCCASION":"Be the first to comment","noRepliesDescription@board:IDEA":"Be the first to comment","messageReadOnlyAlert:BLOG":"Comments have been turned off for this post","messageReadOnlyAlert:TKB":"Comments have been turned off for this article","messageReadOnlyAlert:IDEA":"Comments have been turned off for this idea","messageReadOnlyAlert:FORUM":"Replies have been turned off for this discussion","messageReadOnlyAlert:OCCASION":"Comments have been turned off for this event"},"localOverride":false},"ForumReplyMessage:message:4044998":{"__typename":"ForumReplyMessage","uid":4044998,"id":"message:4044998","revisionNum":1,"author":{"__ref":"User:user:2283996"},"readOnly":false,"depth":8,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:4044414"},"conversation":{"__ref":"Conversation:conversation:3780198"},"subject":"Re: Date Format: Pivot Table vs Source Data","moderationData":{"__ref":"ModerationData:moderation_data:4044998"},"body":"Awesome, thank you for such a prompt reply. That was exactly what I needed.","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"75","kudosSumWeight":0,"postTime":"2024-01-31T10:25:53.126-08:00","lastPublishTime":"2024-01-31T10:25:53.126-08:00","metrics":{"__typename":"MessageMetrics","views":81802},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:3780198/message:4044998","repliesCount":1,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:4045852":{"__typename":"ModerationData","id":"moderation_data:4045852","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:4045852":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:521"},"id":"message:4045852","revisionNum":1,"uid":4045852,"depth":9,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:4044998"},"conversation":{"__ref":"Conversation:conversation:3780198"},"subject":"Re: Date Format: Pivot Table vs Source Data","moderationData":{"__ref":"ModerationData:moderation_data:4045852"},"body":"
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"41","kudosSumWeight":0,"postTime":"2024-02-01T04:40:16.291-08:00","lastPublishTime":"2024-02-01T04:40:16.291-08:00","metrics":{"__typename":"MessageMetrics","views":81489},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:3780198/message:4045852","repliesCount":0,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:4044998":{"__typename":"ModerationData","id":"moderation_data:4044998","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309806":{"__typename":"CachedAsset","id":"text:en_US-components/tags/TagView/TagViewChip-1745505309806","value":{"tagLabelName":"Tag name {tagName}"},"localOverride":false}}}},"page":"/forums/ForumMessagePage/ForumMessagePage","query":{"boardId":"excelgeneral","messageSubject":"date-format-pivot-table-vs-source-data","messageId":"3780198","replyId":"4044414"},"buildId":"HEhyUrv5OXNBIbfCLaOrw","runtimeConfig":{"buildInformationVisible":false,"logLevelApp":"info","logLevelMetrics":"info","openTelemetryClientEnabled":false,"openTelemetryConfigName":"o365","openTelemetryServiceVersion":"25.1.0","openTelemetryUniverse":"prod","openTelemetryCollector":"http://localhost:4318","openTelemetryRouteChangeAllowedTime":"5000","apolloDevToolsEnabled":false,"inboxMuteWipFeatureEnabled":false},"isFallback":false,"isExperimentalCompile":false,"dynamicIds":["./components/seo/QAPageSchema/QAPageSchema.tsx","./components/community/Navbar/NavbarWidget.tsx","./components/community/Breadcrumb/BreadcrumbWidget.tsx","./components/customComponent/CustomComponent/CustomComponent.tsx","./components/messages/TopicWithThreadedReplyListWidget/TopicWithThreadedReplyListWidget.tsx","./components/external/components/ExternalComponent.tsx","./components/messages/MessageView/MessageViewStandard/MessageViewStandard.tsx","../shared/client/components/common/List/UnwrappedList/UnwrappedList.tsx","./components/tags/TagView/TagView.tsx","./components/tags/TagView/TagViewChip/TagViewChip.tsx","../shared/client/components/common/List/UnstyledList/UnstyledList.tsx","./components/messages/MessageView/MessageView.tsx"],"appGip":true,"scriptLoader":[{"id":"analytics","src":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/pagescripts/1730819800000/analytics.js?page.id=ForumReplyPage&entity.id=board%3Aexcelgeneral&entity.id=message%3A4044414","strategy":"afterInteractive"}]}