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 |
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 |
All times are GMT +1. The time now is 05:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com