Problem with date hierarchies

One of the earlier posts referred to date hierarchies, a relatively new functionality that allows the month, quarter, or year to be automatically created based on a column containing a date (depending on the data that column contains). This can cause a problem with previous reports that contain dates, and the user wants to see them as originally created. How to solve this?

Depending on the version of the Office suite, options for working with date hierarchies are turned on or off in different ways. In older versions (Office 2016) you need to run:


And further in Data group should select:

Disable automatic grouping of Date/Time columns in Pivot tables

In recent versions of Excel, as well as the one that comes with Office 365, you need to run:


Then, under this tab, choose the eponymous option.

If, for some reason, you haven’t decide to do this when you created the Pivot table report and you drag a column containing the date into it, additional report dimensions containing the month, quarter or year will be automatically created.

The logical consequence of the event is to exclude additional dimensions and leave only the one where the transaction date was initially located. Excel will display it in the format that it seems most appropriate, and it is often different from what you would like to see within the report. If you run the Field Settings option and select the content formatting option and then find or create a display format that suits you better, you will see that nothing has changed in the Pivot table – the report looks the same as before! This is because it treats the hierarchical columns of Excel as text, and therefore you cannot format them as a number. The solution?

Select the column containing the dates, and then click the Ungroup button. The consequence of this action is that the dates will be displayed in the format you previously set.