Flowing Month Data into a chart based on Weeks?

**BuzzOffSweetheart** · 02-27-2009, 02:49 PM

Hi there!

Here's my problem.. I have a spreadsheet that I am using to try to track Project Man Hours.

What happens is:
I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.

i.e. Project Start - January 2009
Phase 1 - 4 weeks with 1.5 Men
Phase 2 - 20 weeks with 2 Men.

What I want to happen is:
Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.

i.e.
Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.

Does any of this make sense?

I'm terribly sorry but I'm having a hard time explaining this.. I'm going to attach my spreadsheet so you can understand what I mean..

Eventually I want to have a data input worksheet and a manhour chart report for each Project and then all the projects flood into one master chart for all Projects.. And then I want to be able to email that and the individual reports via the worksheet with some sort of macro button or something that opens up a Lotus note new email automatically with the worksheet to send off to who my boss chooses...

But I'm going to take it one step at a time so I dont overstay my welcome.. haha..

Thanks in advance for anyone's help!

*Crossposted here http://www.mrexcel.com/forum/showthread.php?t=374394 because I, like everyone else of course, is on a deadline.. haha. Thanks again!

**BuzzOffSweetheart** · 03-02-2009, 09:39 AM

Still haven't had any luck..

I tried "=StartDate+(Duration*7)"

I dont think that is going to work.. Any other suggestions on how to flood in the month data based on weeks?

If I use:
January 2009 = Start Date
Phase 1 = 4 Wks and 1 man
Phase 2 = 2 Wks and 2 men
Phase 3 = 3 Wks and .5 men

I need it to automatically calculate..
January - We need 1 men
February - We need 2 men for the first two weeks then .5 men for the last two weeks.
March - .5 men for the first week

If I use the simple math way - It doesn't work right..

Like If I put my Start Date as January 2009 And input my phases:

PHASE WEEKS
Proposal 6
Estimate 6
FEED 24
DD 6
Proc 6
CloseOut 2

Then if I do the Start Date + the Week amount per phase multiplied by 7 I get:

February-09
February-09
June-09
February-09
February-09
January-09

And if I adjust the formula to pull the Start Date from the previous start date just calculated:

February-09
March-09
September-09
October-09
December-09
December-09

It starts to work.. but it doesn't still..

Any ideas?

Thanks!