Changing the Date Format for GROUPED dates in a PIVOT TABLE

Question

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.

Answer 1

I was having the same problem. What I have done was to right-click on a value for which I want to change the view on the pivot table and selecting "Ungroup" option. This brings back the reguler view format. I hope this can help you.