Gantt Chart

[centerNegative]

I've worked out a fairly decent Gantt-style chart for use in projec
management that charts out bars over a timeline for different phases o
a project, given a start date and duration for each phase. The problem
am having is this project schedule's timeline needs to be based only o
weekdays. I'd like the timeline, showns a series of dates along the
Axis or Value Axis, to be broken up into units of five and display th
proper dates for only Mondays through Fridays, work weeks, leaving ou
weekends altogether so the chart bars don't get broken up.

Is there any way to leave out weekend days/date from both the Y/Valu
Axis and the graph??

 

[centerNegative]

Has anyone had a chance to review this? I have now spent a full week
trying establish a timeline/Gantt/project schedule-style chart that
covers only weekdays and haven't had any luck. Is there possibly a way
to have the charting feature read all values from cells - i.e., series
labels, data labels, catagory labels, value labels so that I can just
fill them in with the values I want and have them appear in the chart?

I really wouldn't mind filling in all these values myself and having
the chart just use them in appearance. All I want out of the chart is
graphic usability.

I've seen many posts about people having timeline axes that displayed
weekends when they didn't want them displayed and solved the problem by
switching the axis from Time-scale to Category. I haven't even been able
to re-create this problem or solution. Is there a way to accomplish this
with overlay charts? Please, please, I am now *begging * for help.

Here's basically what I am trying to achieve using one task:

Code:

 

[Jon Peltier]

Time scale to category won't work on a value axis.

I'd suggest recoding the dates into values, so Mon to Fri of week 1 are 1 to 5, Mon
to Fri of week 2 are 6 to 10, etc. Use these in the chart. Then use this technique
to put some dummy labels onto the horizontal axis of the chart:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[centerNegative]

Ah, I see. I haven't been able to get this concept to work yet but I'l
give it another shot and post back with my success or lack thereof..
Thanks in advance

 

[centerNegative]

Jon,

What I haven't been able to do from the get-go is even place the date
values on any category axis and produce a working chart, so I still
don't even understand the principle behind getting that working.

What I'd really like to know, is I've reviewed your tutorial here:
http://peltiertech.com/Excel/Charts/GanttChart.html and I am wondering
if along the bottom axis there is a way to reflect weekly
(workday-only) values.

I can get this to work by creating a dummy series and labeling it along
the bottom but the problem I am having is getting the chart to
distinguish from actual calendar days and and my working formulas which
are based on only workdays (by way of the WORKDAY and NETWORKDAYS
worksheet formulas). So, once I create gridlines at major intervals of
5, the actual dummy series messes things up and gets off scale because
it's tracking from a series that uses every calendar Monday, which it's
still referencing as 7 intervals apart.

The only way around this I've found is manually typing in text values
for each Monday. When I need to label 35 Mondays, though, this just
isn't a quickly feasible or realistic solution. Also, it doesn't solve
the problem of the chart bars being off from one another because of
referencing a duration based on workdays onto an axis computing
calendar days.

 

[Jon Peltier]

This all becomes much more complicated if you need to use workdays
instead of all days. Workdays isn't an easy linear scale of dates
anymore. You need to convert the start and end dates to a number of
workdays from a reference point, then realize your "date" axis is really
just an elapsed workdays axis.

Use a technique like this, with your monday dates as labels and the
elapsed workdays for each Monday as X values of the dummy axis series:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

To easily whip off the Mondays, place your first date in a cell, under
it write a formula incrementing this by 7 (7 days per week), and drag it
down as far as you need.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[centerNegative]

Jon, sorry I didn't get back to you sooner, I've been swamped with m
new position at work that kinda necessitated this whole mess in th
first place. I gotta thank you personally for all your brilliant advic
and your amazing and detailed website, for without any of that,
wouldn't have had the vision and endurance to accomplish what I did.

I'll try and simplify what I did here and, hopefully, at some point i
the not-too-distant future, I'll be able to create some kind of
template that I can share with the community. Just to toot my own horn
it's completely brilliant, utterly effective, and has lead to grea
things for me at my company.

After coming to terms with the fact that Excel would just simply no
spread my date values on the value axis, I decided to convert th
entire scope of the Gantt chart into a simple value of the amount o
days between the start date and end date. Now, I know there is always
better way to do anything but this was my approach: my goal was to bas
a chart on standard workweeks, Monday through Friday, and to labe
every major gridline point with the date of the next Monday but b
broken up into only five minor units for the weekdays. To do this,
needed to know the overall proposed start and completion dates for th
job, which is very simple for what I'm working with (constructio
phases). This actually ended up using less of the WORKDAY an
NETWORKDAYS formulas than I had originally since I had to deal with ra
numerical values instead of actual days and dates.

Now all this is displayed on one sheet but then actually calculate
into simple numerical values on another sheet that the chart ca
display accurately. For instance, on my main sheet, I display th
actual start date for the task. On a separate sheet, I use th
NETWORKDAYS formula to calculate the number of days that task start
after the overall job start date. Therefore, the overall start date fo
the job is considered as 0, and if a task starts on the same day, i
will have a value of zero. If a given task starts the day after th
overall job starts, it has a start date value of 1. If I have a jo
that takes five weeks, I set the value axis' minimum and maximum to
and 25 and it all works on that principle, by converting date value
into simple numerical values that the chart can easily display.

I'm sorry I really don't have more to give you right now. I would shar
the spreadsheet I've developed but it's really in a proprietary forma
right now specific to my job and company and it's too complicated to g
into here without posting ten pages of tutorial.

Needless to say, the three major points to getting this to work ar
these: first, all date values need to be converted to a numerical valu
(as in an amount of days, not Excel's built-in serial number format
that corresponds to the value axis' numerical scale (which in mos
cases should be the number of weeks your job will take multiplied b
five for computing workdays). Secondly, you will need to create a dumm
axis/series, which is basically described here
http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html. This will hav
a number of points equal to the number of Mondays to be displayed on th
chart, and all these points will have a value of 0. Last, you will nee
the XY Chart Labeler as described in the link above. Of course, you ca
do without it, but I don't recommend entering in dozens of dates b
hand. To get this to work, all you need is a corresponding range o
dates for every Monday to be displayed that matches the amount of zer
value points you have on your dummy series.

Again, I'm sorry I couldn't be more clear for everyone who's had a
much frustration with this as I but hopefully in the near future I'l
be able to better share my solution and level out the learning curve.

Thanks again go to Jon Peltier (http://peltiertech.com) who has almos
single-handedly provided the means to end probably my greatest turmoi
ever with Excel

 

[centerNegative]

Actually, here's a quick glimpse of what I was able to accomplish:
'
http://www.centernegative.com/mmds/ganttchart.jpg'
(http://www.centernegative.com/mmds/ganttchart.jpg)

The chart operates in 'real time', showing a different color for the
amount of a days a task is completed up to the current date. I haven't
gotten into drawing dependency lines and whatnot because that is not as
easily automated. What I have works well enough for now. It has also
allowed me to create tables that show projected monthly spending and
billings which is where the real importance lies.

 

[Jon Peltier]

Thanks for the testimonial. You've taken this very far, and it looks
very good.

When I can get people to think outside the box, as you have demonstrated
with your time scale axis labeling system, I feel that I've accomplished
something. What did we do in the old days, either with paper and ruler,
or with the most rudimentary plotting drivers? We measured the paper,
decided so many inches horizontally were worth so many units of the X
variable, and scaled the data accordingly; same with Y. Some of the best
charts I do are fancy dynamic charts with fully automatic scales linked
to cells, with custom labels, multiple scales in separate sections of
the chart including partial cycle log and reciprocal scales all in the
same chart. They do this without any VBA, but only with worksheet
formulas and a scaling philosophy similar to the first I ever used, when
I had use of an old (new at the time) HP-85 desktop computer (with a 3"
wide CRT screen and a thermal paper printer) to drive a two-pen plotter
by gunning an arcane version of BASIC to send HPGL commands to the
plotter. Excel's nice, it can give you axis scales and all, but you
don't have to use them if you can draw better ones yourself.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______