SOLVED

Date Formatting in a Pivot Table

Copper 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.

8 Replies
best response confirmed by Lorrie Griffin (Copper Contributor)
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"

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!

Thank u for the information.

@Wyn Hopkins 

Thank you!

@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.

@EtienneCHULg  I'm not sure but maybe that's a windows setting

WynHopkins_0-1642236623297.png

 

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.
1 best response

Accepted Solutions
best response confirmed by Lorrie Griffin (Copper Contributor)
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"

View solution in original post