Jon, sorry I didn't get back to you sooner, I've been swamped with my
new position at work that kinda necessitated this whole mess in the
first place. I gotta thank you personally for all your brilliant advice
and your amazing and detailed website, for without any of that, I
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 in
the not-too-distant future, I'll be able to create some kind of a
template that I can share with the community. Just to toot my own horn,
it's completely brilliant, utterly effective, and has lead to great
things for me at my company.
After coming to terms with the fact that Excel would just simply not
spread my date values on the value axis, I decided to convert the
entire scope of the Gantt chart into a simple value of the amount of
days between the start date and end date. Now, I know there is always a
better way to do anything but this was my approach: my goal was to base
a chart on standard workweeks, Monday through Friday, and to label
every major gridline point with the date of the next Monday but be
broken up into only five minor units for the weekdays. To do this, I
needed to know the overall proposed start and completion dates for the
job, which is very simple for what I'm working with (construction
phases). This actually ended up using less of the WORKDAY and
NETWORKDAYS formulas than I had originally since I had to deal with raw
numerical values instead of actual days and dates.
Now all this is displayed on one sheet but then actually calculated
into simple numerical values on another sheet that the chart can
display accurately. For instance, on my main sheet, I display the
actual start date for the task. On a separate sheet, I use the
NETWORKDAYS formula to calculate the number of days that task starts
after the overall job start date. Therefore, the overall start date for
the job is considered as 0, and if a task starts on the same day, it
will have a value of zero. If a given task starts the day after the
overall job starts, it has a start date value of 1. If I have a job
that takes five weeks, I set the value axis' minimum and maximum to 0
and 25 and it all works on that principle, by converting date values
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 share
the spreadsheet I've developed but it's really in a proprietary format
right now specific to my job and company and it's too complicated to go
into here without posting ten pages of tutorial.
Needless to say, the three major points to getting this to work are
these: first, all date values need to be converted to a numerical value
(as in an amount of days, not Excel's built-in serial number format)
that corresponds to the value axis' numerical scale (which in most
cases should be the number of weeks your job will take multiplied by
five for computing workdays). Secondly, you will need to create a dummy
axis/series, which is basically described he
http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html. This will have
a number of points equal to the number of Mondays to be displayed on the
chart, and all these points will have a value of 0. Last, you will need
the XY Chart Labeler as described in the link above. Of course, you can
do without it, but I don't recommend entering in dozens of dates by
hand. To get this to work, all you need is a corresponding range of
dates for every Monday to be displayed that matches the amount of zero
value points you have on your dummy series.
Again, I'm sorry I couldn't be more clear for everyone who's had as
much frustration with this as I but hopefully in the near future I'll
be able to better share my solution and level out the learning curve.
Thanks again go to Jon Peltier (
http://peltiertech.com) who has almost
single-handedly provided the means to end probably my greatest turmoil
ever with Excel.
--
centerNegative
------------------------------------------------------------------------
centerNegative's Profile:
http://www.excelforum.com/member.php...o&userid=24921
View this thread:
http://www.excelforum.com/showthread...hreadid=387621