Solved

Pivot Table and Group by Year, MOnth

Posted on 2014-10-06
6
207 Views
Last Modified: 2014-10-06
Hello,

I thought there was an easy way to group dates in a pivot table for example by year and then month and even weeks.  In the attached, I do not see how this can be done.   I thought I remembered seeing within the field list, for any date, it would furterh drill down to years, months, weeks even.  I do not see this functionality in the attached.  

How can I Group the attached pivot by year and then month?  I would then want to be able to filter for anyone of those criteria.

thank you for the help.  attached is in 2010.
EE-key-dates.xlsx
0
Comment
Question by:pdvsa
  • 3
  • 3
6 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40364538
please find attached.
F--EE-key-dates.xlsx
0
 

Author Comment

by:pdvsa
ID: 40364545
how did you do that?
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40364547
the problem caused blocking the grouping was the column on the source data that had blank cells and also some cells with text.

I replaced blank and text with 1/1/1930  a dummy date and then refresh the pivot table and then filtered out the dummy date and then grouping worked , please see attachment.


please note that for a pivot to work the grouping of dates,  the source data needs Not to be blank nor any text.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40364566
0
 

Author Comment

by:pdvsa
ID: 40364642
oh so I see for the grouping by year functionality to work then there cant be blanks or any text inthe columns?
0
 

Author Comment

by:pdvsa
ID: 40364645
I see you have to rt click the field and then select Year.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question