grouping dates in pivot table: calculated field?

[katy]

I have a table of data, which lists details of events. Column D contains the
date of the event (dd/mm/yyyy). I am using pivot tables to summarise the data
in various ways. One thing I would like to do is have a pivot table to show
the number of events per region per year.

I know I can do this by adding a new column ("Year") to my data table,
containing the formula =year(D2), and then use Year as the column field in my
pivot table (with Region as the row field)

BUT can I create a calculated field (or Item??) in the pivot table so that I
don't have to insert the additional column in my data table?

If I select Formulas > Calculated Field from the Pivot Table menu, I can
create a new field (Name: Year. Formula: =year(Date)), but then when I try to
drag that field to the column area of my pivot table it tells me "The field
you are moving cannot be placed in that PivotTable area"

Is there a way to do summarise my data by year, without having to have the
additional Year column in my data table? (I know I could use Date as the
column heading in the Pivot, but this creates a very wide table, and it's
pain to then have to select every 2007 date and then Group them together!)

Thanks
Katy

 

[RichardSchollar]

Hello Katy

You don't need a calculated field/item - you can simply use the
Grouping feature of pivot tables. Select the Date field (presumably a
Row field) and right-click and choose "Group and show detail">Group.
You will be presented with a dialog where you can choose to group by
year. Should you still want detail down to the specific dates, then
you can simply insert another Date firld into the Row fields.

Best regards

Richard

 

[katy]

Many thanks for your advice. I figured it out after I had posted my
question! I hadn't been able to group by date before because my Pivot Table
was based on a data range which included blank rows, and I have since found
out that you can't group by date if any of the date cells are blank. (I have
fixed that by basing my Pivot on a named range and naming the range using an
=offset(....counta()) type formula)

Anyway, having fixed that, now I have another question:
is there a way to group by year but starting with a specified month? (I
know you can group by week by selecting 'Days' and specifying the starting
date and the interval as 7 days).

If I group by year, the columns of my pivot table become 2005, 2006, 2007,
2008. But what I really want is the columns by Financial Year (in our case a
financial year runs Jun-May). So I want Jun05-May06 in one column,
June06-May07 in the next column, etc. Any suggestions?