Charting construction schedule

[Calamity Jane]

I need to produce a schedule chart for construction projects. The X axis would be a timeline. I want 2 'floating' bars for each project. There would be a design bar (starts at design start date and ends at design end date) as well as a construction bar (const. start date to end date). Y axis is Projects.

My data is arranged in 5 columns: Project Name, Design Start Date, Design End Date, Construction Start Date, Construction End Date.

Any ideas?


[ This Message was edited by: Calamity Jane on 2002-02-21 09:08 ]

[Mark W.]

See the Tip of the Week at http://www.mrexcel.com/index.html

[Calamity Jane]

Mark: Thanks for your prompt response! I was just experimenting with that solution.

The only problem is that I have 2 sets of dates and would like them to be able to overlap. Some of our CONSTRUCTION begins before the DESIGN is complete. I actually have a graphics program that does what I want (XACT) but importing the pictures into Publisher, Powerpoint, and Word causes the text on the graph to be less clear than is desirable by certain executives...

Could I set up 3 different conditions so that when they overlap I get a patterned fill? :?: I'm going to try and get that to work for now.

[Mark W.]

Don't know what you mean by "overlap". Aren't the tasks overlapped in the last diagram of the tip?

[Calamity Jane]

I have multiple construction projects and need to plot two sets of start and end dates for each. Project A might have these dates:

Design Start: March 2001
Design End: February 2002
Construction Start: December 2001
Construction End: June 2003

The design section would be a blue 'bar' and the construction section would be a red 'bar' for each project. As you can see for Project A, Design does not end before Construction begins. One solution would be to plot this on two rows for each project, but I know the bosses won't like that look, so I'm working on alternatives.

Don't use too much of your valuable time on this monster. I just thought maybe somebody had encountered this type of monster before.

[Mark W.]

Typically each task or activity in a Gantt chart is displayed on a separate line. "Design" and "Contruction" are separate tasks within a given project and, thus are on separate lines.

[ This Message was edited by: Mark W. on 2002-02-21 09:47 ]

I suppose you could do two seperate Gannt charts, each identical apart from the dates data, then snapshot the bars area of one of them making the image no-fill colour and pop it on top of the other, slightly offsetting it so you can see the bars overlapping

you might have to experiment with the gridlines so they don't look all skew or merged

if you link the data on the first to the 2nd, any changes on one will be reflected on the other so you won't need to enter any common data twice

(snapshot makes an image which *updates* as the scource cells update.... the icon is in the tools toolbar)

I just tried it with a couple of blue and red bars and they overlap quite nicely....

Hope this helps
Chris

[Calamity Jane]

Thanks Chris, I'll add that to my list of possible solutions.

[meburke]

I've just gone through this, and to make the cart portion easily viewable, you may want to merge your rows in the task area as 3-row cells. This will leave a clear space above the bar on the right.

You will have to link the start and finish dates to a scale that fits on the spreadsheet. If you are using a daily schedule, remember you only get 256 columns, to you will have to spread the chart across more than one worksheet if you choose not to format your project vertically down the pages.

There are actually a couple of pretty good add-ins that you can buy for far less than you are going to spend on building a Project Management spreadsheet.

I the prject is fairly small, there are instructions on how to make a Gantt chart using the Chart Wizard in the MS knowledge base. It's nothing fancy, but it does scale and it does work.

Mike

[MJK]

The link for the Gantt Chart posted by Mark W. [ http://www.mrexcel.com/index.html ] is not working. Where else can I find this information.

Thanks
MJK