Hi can anyone help with this problem

I have a table of sales data (circa 100 records) in Excel from SalesForce which provides a manhour forecast for a number of engineering disciplines. Projects run for multiple weeks from a start date. Sales are typically forecast up to 9-10 months in the future. Typical data below

Week Number Start Opportunity Name Close Date Duration End Date Week Number End Fire Safety Hours Fire Protection Hours Loss Preventon Hours Fire & Gas Hours Design Hours Proj Mgt Hours
1 Hydro calcs 1/01/13 1 8/01/13 2 70 80 90 100 110 120
1 More Hydro calcs 5/01/13 2 19/01/13 3 70 80 90 100 110 120
2 Even more Hydro calcs 12/01/13 2 26/01/13 4 66.5 76.0 85.5 95.0 104.5 114.0
3 New Hydro calcs 19/01/13 3 9/02/13 6 63.2 72.2 81.2 90.3 99.3 108.3


I need to sum hours for each week, per discipline, for each record with hours in that week (including hours from projects in previous weeks).

Pivot tables can provide the sum function if required however I think need to create an array of weeks / hours for each unique entry first ...

Any good ideas appreciated !