• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9434
  • Last Modified:

Why won't my pivot table slicer group the dates into months, with the new data?

I am currently working with a pivot table someone else created. The data set has dates in one of the columns in the correct format. The slicer has the dates categorized by month and excludes the dates in which there was no data.

Here is my problem: I added a few new months of data. Everything is calculating just fine in the Pivot table. However, my slicer where I should be able to pick the month now has these new months listed as all their individual dates. I want to put the December dates under "December" and so on. The data that was present before I loaded anything additional still has this feature.

How do I group these dates, to be able to them as months? By the way, the grouping function is grayed out.
0
lora2014
Asked:
lora2014
  • 2
1 Solution
 
byundtCommented:
I can't be sure (since you didn't post a workbook that demonstrates the problem), but the symptoms sound like the new dates are text instead of date/time serial numbers. To check, try changing the format of the new dates. If you can't change it, the date data are text that looks like dates.

To convert text into real date/time serial data, here are two good methods:
1.  Select a blank cell, then copy it
2.  Select the data to be converted
3.  Use the Paste Special...Add item
4.  Format the data using one of the date formats

1.  Select the data to be converted
2.  Open the Text to Columns menu item
3.  Choose Delimited in the first step of the wizard
4.  In the second step of the wizard, uncheck all the delimiter options
5.  In the third step of the wizard, select the column with your dates, and make sure you choose MDY or other date specification for your raw data
6.  Format the data using one of the date formats
0
 
lora2014Author Commented:
Thank you for your response. I however found a solution from my colleague as follows:

1. Remove all items in the Pivot table from the Rows Tables section
2. Add the dates to the rows (turn off any filters)
3. Select the dates - ungroup
4. Re-add the dates to the Rows tables section
5. Select the dates - group
6. Choose to group by month and year
7. Insert a slicer for the date and year
8. Remove the dates from the Rows table
9. Re-add any items removed in item 1

The issue was that with there being so many filters in the Rows Table, the dates could not be manipulated at all.
0
 
lora2014Author Commented:
Because...it worked. There was no issue with the dates data, just the Pivot Table. It had too many filters going in the rows.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now