Excel Help: Gantt Chart using VBA 1

[param200]

Hello,

I have an excel data sheet that has the following information:

Project Prj St Prj End Asset ADt Bdt
Project A 01/01/06 05/01/06 Asset1 01/15/06 02/15/06
Asset2 01/15/06 02/15/06
Asset3 01/15/06 02/15/06
Project B 04/01/06 08/01/06 Asset2 04/15/06 06/20/06
Asset4 05/01/06 07/20/06
and so on..

I need this information displayed in a Gantt Chart format, except that all the dates pertaining to particular asset needs to be on one line but different color (i.e for Project A, Asset1 - The ADt should be yellow,BDt should be blue etc..)

Can someone please help.

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827

Each stacked bar represents a number of days.

In constructing a gant chart, keep in mind that the first bar (from left to right) is a start DATE -- the number of days since 1/1/1900, so you will want to set the MINscale to a more recent date. ALSO the first bar is EMPTY.

The second bar, and all following, is a DURATION -- the number of days BETWEEN the start and end of something.

The third bar may be the number of days between the finsish of one thing and the start of another thing, so it would be empty.

etc.......

Skip,

_[red][/red]

 

[SkipVought]

I also recommend reformatting your chart source data ...
[tt]
Project Asset ADt Bdt Duration
Project A 1/1/2006 5/1/2006 120
Project A Asset1 1/15/2006 2/15/2006 31
Project A Asset2 1/15/2006 2/15/2006 31
Project A Asset3 1/15/2006 2/15/2006 31
Project B 4/1/2006 8/1/2006 122
Project B Asset2 4/15/2006 6/20/2006 66
Project B Asset4 5/1/2006 7/20/2006 80
[/tt]
Also on the caregory AXIS -- reverse the order.

I did one in about 5 minutes, reformatting the data and formatting the chart.


Skip,

_[red][/red]

 

[SkipVought]

"The ADt should be yellow,BDt should be blue "

Does this mean that Adt and Bdt are MILESTONE EVENTS and not start-end dates?

Skip,

_[red][/red]

 

[param200]

Hi Skip,

well the reason I'm having problems is because its multiple dates..

Here's what should be displayed

Project A-Bar Chart should be displayed for Prjst & End dts
Asset1(which belongs to Project A) - All dates Adt, Pdt etc should be displayed on one bar chart with different colors corresponding to each date..i.e. the time between Adt and Pdt should be yellow, Pdt and Bdt red...etc

Thanks in Advance

 

[SkipVought]

Project start and end dates CANNOT be displayed on the same bar with items within the project.

If you need bars with multiple parts, I already described how that's done. You could have a dozen bars in a project for different items of different colors. Think DURATION.

If an end date overlaps the nect item's start date, you will have to do some tricky things with your columns/rows. I'm not sure that can easily be done.

Please post an example of what you want with REAL DATA.



Skip,

_[red][/red]