Date Formatting in a Pivot Table

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.

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


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!!!


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