Forum Discussion
Solved
Date Formatting in a Pivot Table
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.
- 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"
8 Replies
- I've had the same issue; formatting the specific dates don't display the same way in the Pivot graph. Try selecting the entire column that contains the dates in your Pivot Table (rather than the specific cells that contain the dates), and then format your date preference for the entire column.
- 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"Wyn Hopkins thanks, dates now show as dd-mm-yy (after ungroup). Also, I ticked "Disable Automatic Grouping of dates", but still I can't change the format to dd/mm/yyyy for instance.
Thid did not happen with Excel 2013.
Thank you!
- Thank u for the information.
