After you create a pivot table with the date field, the date format is fixed. Even if you right-click the cell and choose Format Cells… you won’t be able to change it.
In this case, it will stay mm/dd/yyyy.
The problem here is that Excel doesn’t store this value as date or number but as text. Even if you try to sort by the date it will treat it as text. You can see it in the image below.
Check format date
You can check it by yourself by saving the Excel file as an XML file.
To do it, choose File >> Save As >> Browse. From the Save as type, choose XML Spreadsheet 2003 (*.xml).
Open the file in a text editor and navigate to the name of a worksheet. In our case it’s mypivottable.
Solution #1 – grouping cells in source data
The first solution is to convert date fields into three fields: Year, Month, Day. You can do it using YEAR, MONTH, and DAY functions.
Solution #2 – grouping cells in PivotTable
If you are using Excel 2016, it’s probable that the data is going to be displayed in a different format than it’s formatted inside a table: e.g. “Day-Month” or “d-mmm”.
If you want to change it, right-click and then ungroup Ungroup (PivotTable Tools >> Analyze >> Group >> Ungroup).
To prevent this from happening in the future, go to File >> Options >> Data and check “Disable automatic grouping of dates”.
