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.

6

10 Answers

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.

2

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.

If you select the columns where the date is broken up (within the pivot - years, quarter, etc.) and go data-ungroup. It will get you to the date format.

1

Baris Sari's answer is partially correct. You do have to Ungroup the dates, which then allows you to set any date format you want.

Select all the dates in your PivotTable then right click and select Ungroup. Then right click again on your selection and hit Format Cells. The first tab in the window that opens relates to numbers.

This worked in Excel 2016 and 2010.

1

I just reformatted the date-time field that was the data source for the pivot table (which was dd/mm/yyy hh:mm:ss) to text (ddddd.ttttt) and then when I refreshed the pivot table it appears in the date-time format that I have selected.

There seems to be a new option "Group Field" (marked "7", for some reason. Click this and you get radio button options for Month/ Day/ Hour, etc. Select your preference and click OK. Hope this works for you too!

In your Raw Data, make that column with the dates as Text and in the pivot select the column and change it with Short Date from General. It worked for me.

I was able to solve this issue by manipulating the inner OOXML(Office Open XML) of the Excel document. If you do not know how to open an excel document (to view the xml documents it is composed of internally) please reference one of these links:

• "Best way" Use Visual Studio extension (OOXML Package Editor) to open/edit excel file directly:

• Or use a "simpler" Chrome Based Extension (Drag and Drop Excel file onto it): (remember to download your new file after saving changes to it)

• Or "least advised" way

  1.) Change your filename from "MyExcelFile.xlsx" to "MyExcelFile.zip"
  2.) Unzip the file
  3.) Change the format (as explained below)
  4.) Zip it all back up and change extension to .xlsx
  5.) Warning, This method often corrupts my files, but not always, so I use OOXML Package editor in Visual Studio instead.

Regardless of your method, now navigate to the "PivotCache" for your pivot table. Inside my 'unzipped excel file', the file path is: Root > xl > pivotCache > pivotCacheDefinition1.xml

My copy has 800 lines of xml or so, and I imagine it could be a lot more than that even, but focus only on the portion you want to reformat. (make sure you prettify your xml so it is not one long condensed line, if using Visual Studio Ctrl+K,Ctrl+D)

Search for the attribute: groupBy="months"

my sample finds

 <cacheField name="Months" numFmtId="0" databaseField="0"> <fieldGroup base="2"> <rangePr groupBy="months" startDate="2019-01-02T00:00:00" endDate="2019-12-27T00:00:00"/> <groupItems count="14"> <s v="&lt;1/2/2019"/> <s v="Jan"/> <s v="Feb"/> <s v="Mar"/> <s v="Apr"/> <s v="May"/> <s v="Jun"/> <s v="Jul"/> <s v="Aug"/> <s v="Sep"/> <s v="Oct"/> <s v="Nov"/> <s v="Dec"/> <s v="&gt;12/27/2019"/> </groupItems> </fieldGroup> </cacheField> </cacheFields>

...And all you have to do is replace the "Jan" with "1" or whatever string representation you would like for each month!

taking this one step further, but still answering the question of how to format a group by column, is to format groupBy="days" which results in

 <fieldGroup par="5" base="2"> <rangePr groupBy="days" startDate="2019-01-02T00:00:00" endDate="2019-12-27T00:00:00"/> <groupItems count="368"> <s v="(blank)"/> <s v="1-Jan"/> <s v="2-Jan"/> <s v="3-Jan"/>...

The list goes on and on, 368 elements to be exact. 365 days, plus a first and last element and it includes feb 29th. If you are trying to format a row that is grouped by days, these are the elements to change. ie:

 <s v="1-Jan"/>

You could change them to Chinese, for instance (or anything you like, you just need 366 element replacements)(again, leave first and last element alone).

so "1-Jan" "2-Jan" could become

<s v="1月1日"/>
<s v="1月2日"/>...

You treat these the exact same way I showed for Months, previously. Days, however, are more tedious to do by hand, since you need an element for each day, so I made a C# function you can modify to quickly spit the elements out to file (to copy and paste into your .xml)

 public void GenerateExcelGroupedDateFormatPivotTableElements() { int year = 2019; var sb = new StringBuilder(); for (int i = 1; i <= 12; i++) { var daysInMonth = DateTime.DaysInMonth(year, i); for (int j = 1; j <= daysInMonth; j++) { //Modify template below as you wish, (uses String Interpolation $) and (Verbatim @ symbol) //Full Month Name: ie January-day string monthName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(i); sb.AppendLine($@"<s v=""{monthName}-{j}""/>"); //or Chinese sample //sb.AppendLine($@"<s v=""{i}月{j}日""/>"); } } File.WriteAllText("_ExcelPivotTableGroupedDateHelper.txt",sb.ToString()); }

Important, if you use this function to spit out your days, manually add in Feb 29th, if your original set that you are pasting over has a Feb 29th in it (as mine did).

Disclaimer, I am not an excel expert and there may very well be a better way to do this within the OOXML of an excel sheet. This is simply what I discovered today and thought I'd share what I found since I could not find a posted answer myself.

Follow up do's and don'ts advice for this would be appreciated.

Happy coding!

0

I have tried two things:

1. I just changed cells' format as usual.

2. Selected more than one item on grouping (using Control+click on windows or Command+click on mac) source

ps.: I specified how to select more than one item because it is not much intuitive.

1

I selected one of the column headings and clicked ungroup. My problem was the pivot table had mmm headings and I wanted m/d/yyyy corresponding to my raw data.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy