Pivot table - multiple records with same year??

[Don]

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

 

[Miguel Zapico]

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