Breaking out dates in Pivot Tables

Topic

New Reply

In a number of Pivot Table (PT) examples that I have seen on the web, if Date is one of the variables in the PT Fields such that, when it is placed in a Row or Filter position within the PT,  Excel adds additional Fields — Year/Month/Day, etc.  My problem comes when I try the “same thing” on one of my Pivot Tables, I do not get the “new fields” in the list of PT Fields.  My date simply remains as a “date”, .e.g., in the web example, a date such as 10/14/2011 (MDY) when brought into the PT would spawn at least two new PT Fields, Month and Day.  When I bring a similar date into one of my PTs, this “creation” of two new variables does not happen.  I am thinking that there is a “switch” somewhere that needs to be activated <on> or <off>.  Any insight that anyone can provide will be greatly appreciated.  TIA.

Ron M

Note:  I am running Win 10 Pro and Office 365 (or whatever MS calls it today?)

Reply | Quote

Replies

> Any insight that anyone can provide will be greatly appreciated.

It’s harder to post detailed advice here these days with the wordfence watchdog merrily munching complicated comments, but maybe something like this?

1) assuming date is specified as ROW label, in resulting pivottable click on date value (beneath row label heading) > right-click > Group > select desired fields (e.g., Years, Months, Days) > click OK

2) to separate out field(s) from resulting “compact” column, if desired, click on field value (beneath row label heading) > right-click > Field Settings > Layout & Print tab > uncheck box to “Display labels from the next field in the same column (compact form)” (…or, for an alternative view, try clicking radio button to “Show item labels in tabular form”)

Note: above guidance verified on Office 2013, but hopefully “365” version will be similar enough to get you started…

Hope this helps.

Reply | Quote

Hello, same anon here.

Currently using Office 2016 and just experienced related issue when creating pivot table using Excel. In this case, when I added date field to pivot table Excel automatically disassembled date field, creating additional date-related fields that I neither requested nor wanted.

Fortunately (for me), I was able to change (disable) this mode of operation with the following checkbox:

File > Options > Advanced > Data section (scroll down) >
[v] Disable automatic grouping of Date/Time columns in PivotTables

In your case, maybe this checkbox is also checked – and maybe this is the “switch” you were seeking?

Hope this helps.

1 user thanked author for this post.

IT Manager Geek

Reply | Quote

anonymous, thanks for the insight.  I have taken some time to get back to this as I am the sole caregiver for my wife and I am not able to devote as much time to things like this as I would like to…time passes…

I found that if I highlighted all the dates in the Date column and then reformatted them the same, then I can get the results that I want.  I will however, check out your insight on date formatting defaults in PTs.

Ron M

Reply | Quote