Spreading Hours based on Project Phases and Start/End Date

I have a slightly more complex project that I need some assistance with. I have projects with different durations (including different start and end dates) with a budgeted amount of labor hours allotted to the project. I need to be able to spread those hours throughout the project, based on the start and end date, but also based on a weighted percentage depending on where that month falls in the phase of the project.

For example, I have three different types of labor. Each type of labor budget is spread differently.
Engineering is flat, where the hours are evenly distributed across all months.
Design is 65% in the first third of the project, 25% in the 2nd third of the project, and 10% in the last third of the project.
And Testing is 5% in the 1st third, 75% in the 2nd third, and 20% in the last 3rd.

Each category is allotted its own budget, and I want to make the spreadsheet automatically spread the hours. I have used the formula provided in another thread (http://www.excelforum.com/excel-gene...html?p=2944603) order to get the hours in the right months (based on the start/end date). I just now need to figure out how to weight the hours based on which of the 3 phases of the project it is in. Is there a solution for this type of complexity?

I'll attach my spreadsheet to show where I am at currently, but I know it will need a lot more work to incorporate the weighted labor budgets.

Please let me know if I have not made any sense, It is complicated for me and hard for me to put into words properly.

Labor Budgets.xlsx

Originally Posted by shg

Your projects are always 15 months long, and all start on the first of the month. Those seem to be some simplifying assumptions that might not hold in actual practice -- true, false?

More generally, should allocations not be based on number of workdays in the period of performance, for arbitrary start and stop dates?

Sorry - the projects vary in length and the start/end dates also vary. I just put them in to try to simplify it for myself.

Originally Posted by shg

Your projects are always 15 months long, and all start on the first of the month. Those seem to be some simplifying assumptions that might not hold in actual practice -- true, false?

More generally, should allocations not be based on number of workdays in the period of performance, for arbitrary start and stop dates?

Were you able to figure anything out? OR do I need to try to rephrase what I am hoping to do.

I was able to solve this on my own with a very complex and involved formula. If anyone has an issue similar to this one - please let me know, and I will post the formula.