Pivot table - show monthly data based on start/end date data

A

Andrew

I have project resourcing data in XL the form:
Person | Project | StartDate | EndDate | Percentage

Is it possible to use a pivot table to display this as:
Person in column 1
Month (Jan, Feb, Mar, ...) across the columns
Sum of Percentage for each month that falls within the start/end date

eg. XL data
Person | Project | StartDate | EndDate | Percentage
Andrew | Proj1 | 1/1/2011 | 28/2/2011 | 50%
Andrew | Proj2 | 1/2/2011 | 28/2/2011 | 30%
Adam | Proj1 | 1/2/2011 | 28/2/2011 | 90%

To be displayed in pivot table as:
Person | Jan | Feb | Mar | ...
Andrew | 50% | 80% | 0%
Adam | 0% | 90% | 0%

I know I can build start and end date into a formula but I don't know
how to create the month columns and without those there is no month
entry to compare start/end date against.

Thanks very much for any help.
Andrew
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating occupational sick pay entitlement 0
assistance needed: transpose data from rows to columns 1
Help showing active months between two dates 5
Move data to column based on startdate 1
Display billing month based on start date and end date 5
VBA Pivot Table: Determining item from one field based on anotherfield item 0
Date Labels 2
VBA Pivot Table Error 1

Top