How to Change Date Formatting for Grouped Pivot Table Fields

Bottom line: Learn how to change the date formatting for a grouped field in a pivot table.

Skill level: Intermediate

Pivot Table Grouped Day Field Number Formatting

Changing the Days Field Number Formatting Doesn’t Work

When we group a Date field in a pivot table using the Group feature, the number formatting for the Day field is fixed.  It has the following format “Day-Month” or “d-mmm”.

If we try to change the number format of the Day/Date field it does not work.  Nothing changes when we go to Field Settings > Number Format, and change the number format to a custom or date format.


Changing Number Formatting of Grouped Field Day Does Not Work

The number formatting does not work because the pivot item is actually text, NOT a date.

When we group the fields, the group feature creates a Days item for each day of a single year.  It keeps the month name in the Day field names, and this is actually a grouping of day numbers (1-31) for each month.

We can actually see this list of text items in the pivotCacheDefinition.xml file.  To see that you can change the file extension of the Excel file to .zip, and navigate to the PivotCache folder.

pivotCache XML for Days Group

Since these are text items that represent the days of the year, we won’t be able to change the number formatting of the cells directly in Excel.  However, there are a few workarounds.

Solution #1 – Don’t Use Date Groups

The first solution is to create fields (columns) in the source data range with the various groups for Year, Quarter, Month, Days, etc.  I explain this in detail in my article on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data.

Pivot Table Date Groups vs Source Data Date Groups in Excel

Using your own fields from the source data for the different date groups will give you control over the number formatting of the field in the pivot table.

You can also create a Calendar Table with the groupings if you are using Power Pivot.

Automatic Date Field Grouping

If you are using Excel 2016 (Office 365) then the date field is automatically grouped when you add it to the pivot table.

To Ungroup the date field:

  1. Select a cell inside the pivot table in one of the date fields.
  2. Press the Ungroup button on the Analyze tab of the ribbon.

Ungroup the Date Field to Change the Number Formatting

The automatic grouping is a default setting that can be changed.  See my article on  Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data to learn more.

Once the date field is Ungrouped you can change the number formatting of the field.

Custom Date Formatting on Ungrouped Pivot Field

To change the number formatting on the ungrouped Date field:

  1. Right-click a cell in the date field of the pivot table.
  2. Choose Field Settings…
  3. Click the Number Format button.
  4. Change the Date formatting in the Format Cells window.
  5. Press OK and OK.

Again, this only works on fields that are NOT grouped.  If you group the field again after changing the formatting, the formatting for the items in the Days field will change back to “1-Jan”.

Solution #2 – Change the Pivot Item Names with VBA

If you really want to use the Group Field feature, then we can use a macro to change the pivot item names.  This will make it look like the date formatting has changed, but we are actually changing the text in each pivot item name.

Change Number Formatting of Days Field in Pivot Table with a Macro VBA

The following macro will loop through all the pivot items of the grouped Days field, and change the number formatting to a custom format. By default I set it to “m/d”, but you can change this to any date format for the month and day. Just remember that the item is NOT going to contain the year, since the item is not an actual date.

Download the File

Download the Excel file that contains the macro.

Pivot Table Date Field Group Number Formatting Macro.xlsm (54.2 KB)


The Change Days Field Formatting Macro

Sub Change_Days_Field_Formatting()
'Change the number formatting of the Days field
'for Grouped pivot table date field.
'Source: https://www.excelcampus.com/pivot-tables/grouped-date-field-formatting/

Dim pt As PivotTable
Dim pi As PivotItem

'IMPORTANT: Change the following to the name of the
'grouped Days field.  This is usually Days or the name
'of your date field.

Const sDaysField As String = "Days"

  'Set reference to the first pivot table on the sheet
  'This can be changed to reference a pivot table name
  'Set pt = ActiveSheet.PivotTables("PivotTable1")
  Set pt = ActiveSheet.PivotTables(1)

  'Set the names back to their default source name
  For Each pi In pt.PivotFields(sDaysField).PivotItems
    'Bypasses the first and last items "<1/1/2015"...
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      pi.Name = pi.SourceName
    End If
  Next pi

  'Set the names to a custom number format
  For Each pi In pt.PivotFields(sDaysField).PivotItems
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      'Change the "m/d" format below to a custom number format.
      'Year 2020 is used for leap year.
      pi.Name = Format(DateValue(pi.SourceName & "-2020"), "m/d")
    End If
  Next pi

End Sub

How the Macro Works

The macro first loops the pivot items in the Days field to restore the pivot item name to it’s default source name.  Two different pivot items cannot have the same name.  So this should prevent any errors when changing or trying different formats.

The second loop changes each pivot item to the new format.  It uses the DateValue function to change the pivot item name “1-Jan” to a date.  It then uses the Format function to change the formatting of the date to text.  By default it uses the “m/d” format.  This can be changed to another format with the month and day.  Each item must be unique, so you will want to use both the month and day in the item name.

You could probably separate this macro into two macros, and only run the first reset loop as needed.  The macro takes about 15 seconds to run on my computer because of all the looping.  But it’s not one you will have to run often.

It’s also important to mention that this is running on the pivot items, not the pivot cache.  So the newly named items will only be changed on the pivot table you run the macro on.

Final Verdict

I hope you find that helpful.  I would suggest going with Solution #1 unless you really want to use the Group feature.

Please leave a comment below with any questions or suggestions on how we can improve this.  Thanks so much! 🙂

Please share
Jon Acampora

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 9 comments
kik for computer - February 23, 2018

I was wondering if you ever thought of changing the page layout
of your site? Its very well written; I love what youve got to say.
But maybe you could a little more in the way of content so people could
connect with it better. Youve got an awful lot of text for
only having one or 2 pictures. Maybe you could space it out better?

T Garfield - February 20, 2018

Just FYI- if you have office 365 Excel 2016 and it auto groups your dates, then ungrouping does not let you change format- there is still no number format button. It also won’t sort by date correctly no matter what you do. Idiot proofing gone wrong is so very frustrating! Version 1708 Build 8431.2153.

noha - February 18, 2018

Hi Jon,
Thank you for the comprehensive post. I have a question about graphs as well. When I make a bar-line graph where I have two series ( each has a bar and line graph) so I have two lines and two bars. I need the data points of each line to be aligned with the bar of the same series ( it is always aligned in the middle between the two bars. Please tell me how to make such alignment.
Thank you in advance.

ETHAN - February 18, 2018


Kirk Bausch - February 18, 2018

i was challenged with this last week. perfect timing – thx

DAVE MASON - February 16, 2018

This is definitely a data pet peeve of mine. I tend to go the first option of adding fields to the source data after going crazy trying to reformat the group dates. At least I have better control of that. Thanks for the options, I’ll explore those when I have data and time.

    Jon Acampora - February 16, 2018

    Thanks Dave! Yeah it’s just good to be aware of the issue that we can’t direct change the date format. Have a nice weekend! 🙂

Mark - February 16, 2018

John, could you send the XL file that goes with this article? That way I can try out your suggestions and follow-up if there are issues or questions. Thanks. Mark


Leave a Reply: