Pivot table format date

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