SOLVED
Home

Date Formatting in a Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-212645%22%20slang%3D%22en-US%22%3EDate%20Formatting%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212645%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20not%20been%20able%20to%20format%20dates%20in%20a%20Pivot%20Table%20since%20I%20started%20using%20Excel%202016.%26nbsp%3B%20I%20have%20tried%20a%20number%20of%20fixes%20on%20the%20blog%20to%20no%20avail.%26nbsp%3B%20The%20data%20worksheet%20has%20the%20date%20formatted%20as%20I%20would%20like%20which%20is%2006%2F02%2F18.%26nbsp%3B%20When%20I%20add%20the%20date%20to%20the%20Pivot%20Table%20it%20pulls%20in%20the%20Month%20as%20a%20column%20which%20is%20not%20in%20my%20original%20data%20and%20then%20gives%20me%20a%20date%20column%20that%20looks%20like%202-Jun.%26nbsp%3B%20I%20really%20want%20the%20date%20to%20look%20the%20way%20it%20is%20in%20the%20original%20data.%26nbsp%3B%20Can%20anyone%20help%20please%3F%26nbsp%3B%20I%20would%20truly%20appreciate%20it.%26nbsp%3B%20Let%20me%20know%20if%20you%20need%20to%20see%20an%20example.%26nbsp%3B%20Thank%20you%20in%20advance.%26nbsp%3B%20I%20am%20sure%20it%20is%20something%20small%20I%20am%20missing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-212645%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-329627%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-329627%22%20slang%3D%22en-US%22%3E%3CP%3EExcellent.%20It%20works!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212859%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212859%22%20slang%3D%22en-US%22%3E%3CP%3EWyn%2C%3C%2FP%3E%3CP%3EThank%20you%20so%20much.%26nbsp%3B%20That%20worked!!%26nbsp%3B%20I%20knew%20it%20was%20something%20like%20that%20but%20I%20had%20no%20idea%20that%20it%20groups%20the%20dates%20in%20a%20Pivot%20Table.%26nbsp%3B%20You%20have%20saved%20me%20many%20more%20hours%20of%20anguish.%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20weekend%20and%20summer!!!%3C%2FP%3E%3CP%3ELorrie.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212661%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212661%22%20slang%3D%22en-US%22%3EHi%20Lorrie%3CBR%20%2F%3E%3CBR%20%2F%3ERight%20Click%20on%20the%202-Jun%20and%20select%20Ungroup.%20That%20should%20fix%20it%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20prevent%20it%20happening%20in%20future%20go%20to%20File%20%26gt%3B%20Options%20%26gt%3B%20Data%20and%20tick%20%22Disable%20Automatic%20Grouping%20of%20dates%22%3C%2FLINGO-BODY%3E
Lorrie Griffin
New Contributor

I have not been able to format dates in a Pivot Table since I started using Excel 2016.  I have tried a number of fixes on the blog to no avail.  The data worksheet has the date formatted as I would like which is 06/02/18.  When I add the date to the Pivot Table it pulls in the Month as a column which is not in my original data and then gives me a date column that looks like 2-Jun.  I really want the date to look the way it is in the original data.  Can anyone help please?  I would truly appreciate it.  Let me know if you need to see an example.  Thank you in advance.  I am sure it is something small I am missing.

3 Replies
Solution
Hi Lorrie

Right Click on the 2-Jun and select Ungroup. That should fix it

To prevent it happening in future go to File > Options > Data and tick "Disable Automatic Grouping of dates"
Highlighted

Wyn,

Thank you so much.  That worked!!  I knew it was something like that but I had no idea that it groups the dates in a Pivot Table.  You have saved me many more hours of anguish. 

Have a great weekend and summer!!!

Lorrie.

Excellent. It works!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies