Help with summing up individual resource types to a particular month based on a date range

[pforte]

Hi,

I am making a project plan/financial model in Excel (don't have access to MS Project) and one aspect of it is I have to be able to show how many of a certain type (i.e. Res1, Res2 in attached picture) of full-time employee (FTE) will be needed for a given month.

Each "Major Activity" (shown as Act. 1, Act. 2) has a start and end date that is flexible/reactive based on the number of employees the user wishes to allocate to that activity. What I need to do is come up with an easy way to sum up individually by role (i.e. Res1, Res2) how many people will be needed for a given month over a multi-year period, based on the duration of/whatever activities take place.

I've been wrestling with this one for a couple days now and any direction at all to potentially create a new spark would be greatly appreciated. Happy to expand on the issue as well as it is very hard to explain via text.

Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

 

[pforte]

To give you an idea of what kind of output I am looking for in a simple visual, see the picture below.

Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

Whatever the solution is, it also has to be able to mutually exclusively carry over FTEs to the next month if an activity spans longer than a month (i.e. if there is 1 FTE working from Late January to Late February, the worker's allocation would be ~0.25 in January and ~0.75 in February or whatever the exact calculation is)