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:
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="<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=">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!
