Excel Pivot Table Date Range as Columns

microsoft excelmicrosoft-excel-2003pivot table

I have the following data which I wish to pivot:

Student    JoinDate    ReleaseDate
John       01/05/2011  05/05/2011
James      02/05/2011  04/05/2011

I would like to create a pivot table which shows me a count of students within a given date range:

01/05/2011  02/05/2011  03/05/2011  04/05/2011  05/05/2011
1           2           2           2           1

Now, the issue I have is that I want the pivot table to somehow expand out the dates, I can't figure out how to achieve this with my current data structure.

I can achieve it with the following structure:

Student    JoinDate    ReleaseDate   PivotDate
John       01/05/2011  05/05/2011    01/05/2011
John       01/05/2011  05/05/2011    02/05/2011
John       01/05/2011  05/05/2011    03/05/2011
John       01/05/2011  05/05/2011    04/05/2011
John       01/05/2011  05/05/2011    05/05/2011
James      02/05/2011  04/05/2011    02/05/2011
James      02/05/2011  04/05/2011    03/05/2011
etc...

But this technique doesn't scale very well given the amount of data in use, I'm easily hitting the maximum number of rows for a single sheet in the workbook.

Does anyone have any suggestions as to how I can create the desired pivot table without having a row for each date the student is 'active'..?

Best Answer

If you're willing to try a solution that doesn't involve pivot tables:

Remove the Pivot Date column. Arrange the date range in headers and use the AND() formula to determine if that date falls within the student's tenure. Your data will look something like this:

enter image description here

Here's how the formula in D2 looks like (note the absolute references). Just drag or copy the formula to the rest of the columns and rows, autofill should take care of the rest:

=AND(D$1>=$B2,D$1<=$C2)+0

Number format is set to this: "+";;

The formula for the total number of students per date range is pretty easy. Here's the formula for D26 above. 

=sum(D2:D25)

To add columns, just copy the last column and change the date above. To add students, insert a blank row above the last student (in this case, above Banshee). Record a macro to make it easier for you to update the data. This way you don't have to manually adjust the SUM formula at the bottom row. You can sort the data by Student name, Join date or Release date; the formulas at the right should be fine.

Here's a copy of the spreadsheet shown above (requires MS Excel 2007): http://ge.tt/46auqAN

Related Question