Excel 2007 : Academic Year in Pivot Tables

**smilingkyle529** · 04-18-2010, 11:36 PM

So I have an excel 2007 sheet that contains a list of people and what day they joined an organization for school. Each row of data contains name, date joined, age, group, occupation, etc. I currently have multiple pivot tables from that information that display the number of people who have joined during a year (table for 2003, table for 2004, etc) along with which group they were assigned to (with that number of people assigned that current year).

What I want it to do is have a table for each academic year (example: July 1st, 2003 to June 30th, 2004) instead of each year. Is there any way to do this in the pivot tables?

**DonkeyOte** · 04-19-2010, 02:58 AM

The simplest solution would be to add this year field to your data source as a new column - and use the new column in your PT.

Assume Date Joined is Col B of your data sheet and say Col J is the first blank column available...

J1: Acad. Year
J2: =YEAR($B2)+(MONTH($B2)>6)
copied down
(July 1st 2003 to June 30th 2004 would be 2004 for ex.)

You can then use Acad. Year as the field by which you differentiate in your Pivots (ie as Report Filter)
Note: you will most likely need to revise your Data Source for the Pivot(s) to incorporate the new column.

The Date grouping facilities of Pivots (Quarters etc...) are all Calendar Year based I'm afraid.