Search code examples
excelexcel-2010pivot-table

Changing the Date Format for GROUPED dates in a PIVOT TABLE

I'm working in Excel 2010

Let's say you have a legitimate date field in your raw data with dates such as 1/1/2015. Then you create a pivot table with your date field in the row labels.

Now assume you want to show results by month, so you right click "Group Field" and select to group it by month.

Now, your data is displayed in months with the Mmm format. How would you change it the Mmmm format? Or what if you want to display it as a Number (i.e. Jan is displayed as 1)

How about for dates that have timestamps and you group by Day. The pivot table will display the date as D-Mmm. What if I want MM/DD/YYYY?

Yes, I've already tried changing it through right clicking -> field settings -> number format. It didn't work.

Solution

  • As of Excel 2016, there is no way to change the way that Excel auto formats grouped dates in pivot tables.

    The workaround is to create a new field/column in the source data file with the desired format and use that in the pivot table.