'Issues grouping in excel?
I'm having a little bit of trouble grouping dates in excel. I have created a pivot table that groups a bunch of dates between 3 months together. However, I want to take advantage of the refresh ability of pivot tables--the idea in which when you refresh your pivot table, it will update to whatever you added or took away from the source of data your pivot table is based on.
However, when I grouped the dates between three months together, I noticed if I don't add a date that exactly corresponds to the dates within the grouping, the picot table does not add that entry into the pivot table. For example, if my grouping had the following dates (between May and July) in it:
5/21/2013, 7/18/2013, 6/8/2013
And in my source data spreadsheet had the following date added to it:
5/2/2013
5/2/2013 would not be added to that specific grouping in the pivot table because it is not exactly like the other dates as stated above. I want it so that I can group all dates from 5/1/2013 to 7/31/2013 inclusively as opposed to the dates in the original grouping. Would anyone have an idea on how to do so? Thanks!
Solution 1:[1]
http://www.datawright.com.au/excel_resources/expanding_pivot_tables.htm
im curious as to how you are currently grouping them without adding new ones, there may be a way to work with what you already have especially if you are willing to incorporate some VBA
Solution 2:[2]
Provided your source 'dates' are not text Excel should be able to cope.
I fear (hope?) we were at cross-purposes about which "Group" to use. I meant the one that pops up (as on the left below) when right-clicking one of the Qtr headings in the PivotTable.
Solution 3:[3]
If you have access to add additional formulas in the data you could just calculate "quarter" in an additional column (reference here)
How can I obtain the year quarter from a date?
I tend to always make my groups in the rows of data like this, rather than attempt it using pivot groups. For example, you might also extract "year" with = year(A1) in the rows - so you can easily add it as a pivot row item later for grouping. If your data source is properly formatted as an excel 2007 "table" these kind of formulas will fill themselves downward when you paste new data.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Mr.Monshaw |
| Solution 2 | |
| Solution 3 | Community |