Week estimates rolling up to months

Hi,

I think I posted my question in the wrong forum group. Since my request is more around a macro it should have been posted here. Here's a description of my request.

We are in a scrum agile environment and I working on a spreadsheet that tracks my teams project/stories for each work sprints. The sprints are 2 weeks long (10 work days). The challenge that I am facing is that we have to report the work hours for each performer on a monthly basis. In a month there can be more than 2 sprints with the 3 sprint overlapping between 2 months. The stories for each sprint are estimated for each sprint and in order to get the monthly totals (for 20 work days), the estimates for the sprints overlapping 2 months have to be broken down accordingly to reflect the correct estimate. (i.e. if the sprint runs from 29-Oct-12 to 9-Nov-12, and testing estimates are 30 hrs, the October total should be split accordingly between 3 days for October and the rest 7 for November.

I have attached the sample spreadsheet for your reference. The sprint planning worksheet has the sprint estimates. I have tried looking into SUMPRODUCT and other functions, but with no success. Any help will be greatly appreciated.

Thank you,
VB