When you show subtotals for a pivot table date field, the dates might not be formatted like the rest of the dates. We’ll take a look at why this happens, and how you can fix it.
For example, in the screen shot below,
- the date (in row 4) is formatted as d-mmm-yy
- the subtotal date (in row 8) is formatted as mmm/yy
Format a Date Field
When this pivot table was set up, it just had the dates in the row labels area. I selected all the date cells, and formatted them as d-mmm-yy.
The dates changed to the correct format, without any problems.
Add a Subtotal
Later, I added another field – Category – to the row labels area, and a subtotal was automatically created for the Date field. And that’s when the strangely formatted dates appeared.
The problem occurred because I had formatted the worksheet cells, instead of formatting the pivot table’s date field.
Format the Field
To fix the problem, I had to change the Number Format in the order date field. Here are the steps to do that:
- Right-click one of the date field labels, and in the popup menu, click Field Settings.
- Click the Number Format button
- Select the date formatting option you want.
- Click OK twice, to close the dialog boxes
After you make that change, the subtotal dates will appear in the correct formatting – the same as the date field labels.
Video: Subtotal Date Formatting
To see the steps for fixing the subtotal date format, please watch this short video tutorial.
Or watch on YouTube: Date Formats in Excel Pivot Table Subtotals
Download the Sample File
To test the date formatting, you can download the sample file from my Contextures website, on the pivot table subtotals page:
http://www.contextures.com/excel-pivot-table-subtotals.html
____________