Format Date Field Subtotals

Format Date Field Subtotals

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

subtotaldateformat02

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.

subtotaldateformat04

The dates changed to the correct format, without any problems.

subtotaldateformat03

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.

subtotaldateformat02

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.

subtotaldateformat05

  • Click the Number Format button

subtotaldateformat06

  • Select the date formatting option you want.

subtotaldateformat07

  • 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.

subtotaldateformat01

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

____________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.