Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Gantt Chart
I've worked out a fairly decent Gantt-style chart for use in project management that charts out bars over a timeline for different phases of a project, given a start date and duration for each phase. The problem I am having is this project schedule's timeline needs to be based only on weekdays. I'd like the timeline, showns a series of dates along the Y Axis or Value Axis, to be broken up into units of five and display the proper dates for only Mondays through Fridays, work weeks, leaving out 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/Value Axis and the graph??? -- centerNegative ------------------------------------------------------------------------ centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921 View this thread: http://www.excelforum.com/showthread...hreadid=387621 |
#2
|
|||
|
|||
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: -------------------- __________________ Construction........ | |__________________| | |_._._._._|_._._._._|_._._._._|_._._._._|_._._._._ | 6/6/05 6/13/05 6/20/05 6/27/05 7/04/05 7/11/05 -------------------- -- centerNegative ------------------------------------------------------------------------ centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921 View this thread: http://www.excelforum.com/showthread...hreadid=387621 |
#3
|
|||
|
|||
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 wrote: 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: -------------------- __________________ Construction........ | |__________________| | |_._._._._|_._._._._|_._._._._|_._._._._|_._._._._ | 6/6/05 6/13/05 6/20/05 6/27/05 7/04/05 7/11/05 -------------------- |
#4
|
|||
|
|||
Ah, I see. I haven't been able to get this concept to work yet but I'll give it another shot and post back with my success or lack thereof... Thanks in advance! -- centerNegative ------------------------------------------------------------------------ centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921 View this thread: http://www.excelforum.com/showthread...hreadid=387621 |
#5
|
|||
|
|||
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 he 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. -- centerNegative ------------------------------------------------------------------------ centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921 View this thread: http://www.excelforum.com/showthread...hreadid=387621 |
#6
|
|||
|
|||
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 wrote: 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 he 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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. -- centerNegative ------------------------------------------------------------------------ centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921 View this thread: http://www.excelforum.com/showthread...hreadid=387621 |
#9
|
|||
|
|||
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/ _______ centerNegative wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) | |||
Gantt chart with labels | Charts and Charting in Excel | |||
Gantt Chart with variable width columns - is this possible? | Charts and Charting in Excel |