Fix Date Format in Pivot Table Subtotals

If you show pivot table subtotals for a date field, the dates might be in a format that is different from the other dates in that field. See why that happens, and how you can avoid the date format problem.

Video: Fix Date Format in Pivot Table Subtotals

If you add subtotals to a pivot table date field, the subtotal date format might be different from the rest of the dates in the field.

Watch this 2-minute video to see why the problem occurs, and how you can prevent the problem of different formats.

Subtotal Date Format Problem

You might see different date formats in pivot table subtotals, like the example shown below.

In the headings, the dates have the format: dd-mmm-yy
In the subtotals, the date format is: d/m/yyyy

Why It Happens

This type of pivot table formatting problem can happen if you do this:

select the pivot table date cells
format them like normal worksheet cells, using commands on the Excel Ribbon
later, add subtotals for the date items

The subtotal dates don’t automatically get the format that you applied to the date cells.

Fix Date Format Problem

To avoid the problem, always format pivot table dates and numbers through the Field Settings dialog box.

Here are the steps for this example:

Right-click one of the date field labels
In the popup menu, click Field Settings.
Click the Number Format button
Select the Date format that you previously applied to the date field
Click OK, twice, to close the dialog boxes

The subtotal number format should now match the other dates in the field.

Get the Sample File

To get the sample file for this Subtotal Date Format video, and for other Pivot Table tips and examples, go to the Pivot Table Subtotals page on my Contextures site.

The zipped sample file is in Excel xlsx format, and does not contain any macros.

____________________