09-14-2005, 11:52 AM
|
|
Registered User
|
|
Join Date: 05 Jul 2005
Posts: 19
|
|
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 here: 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.
|