Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table - multiple records with same year??
I am trying to create a pivot table from a w/s of data. One field formatted
as a date (dd-mmm-yyyy) needs to be broke out into its constituent month (mmm) and year (yyyy) in seperate columns. For some reason I am unable to make each instance unique (eg I end up with hundreds of records , that even though are the same year (eg 2007), are represented by a unique record entry)? On closer examination if I put my curser over the original 'Date' field, it also contains a timestamp hh:mm:ss - although this does not appear in the actual field. Originally I thought this may be causing the problem, but having removed this time stamp I still end up with multiple instances of the same year in pivot. I am sure other folk must have encountered this same problem? Is there an effective resolution? Don |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table - multiple records with same year??
If you are not using calculated items or fields, you can group by the Date
column, on both year and month, and you won't have to worry about the time part of the date. To do the grouping, just right click on the date field on the pivot table, select "Group and Show Detail" and then "Group". On the "By" part select Months and Years, and it will split the field into the two ones that you are requiring. Other option is to apply the INT function to the original dates in the raw data, to trim the time part, but that usually involves more work and maintenance. Hope this helps, Miguel "Don" wrote: I am trying to create a pivot table from a w/s of data. One field formatted as a date (dd-mmm-yyyy) needs to be broke out into its constituent month (mmm) and year (yyyy) in seperate columns. For some reason I am unable to make each instance unique (eg I end up with hundreds of records , that even though are the same year (eg 2007), are represented by a unique record entry)? On closer examination if I put my curser over the original 'Date' field, it also contains a timestamp hh:mm:ss - although this does not appear in the actual field. Originally I thought this may be causing the problem, but having removed this time stamp I still end up with multiple instances of the same year in pivot. I am sure other folk must have encountered this same problem? Is there an effective resolution? Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deselecting records in pivot table automatically | Excel Discussion (Misc queries) | |||
Deselecting records in pivot table automatically | Charts and Charting in Excel | |||
Extract multiple records from Excel table | Excel Worksheet Functions | |||
unique records in Pivot Table | Excel Discussion (Misc queries) | |||
"How do I get rid of old records in an Pivot Table? | Excel Discussion (Misc queries) |