#1   Report Post  
centerNegative
 
Posts: n/a
Default 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   Report Post  
centerNegative
 
Posts: n/a
Default


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   Report Post  
Jon Peltier
 
Posts: n/a
Default

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   Report Post  
centerNegative
 
Posts: n/a
Default


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   Report Post  
centerNegative
 
Posts: n/a
Default


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   Report Post  
Jon Peltier
 
Posts: n/a
Default

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   Report Post  
centerNegative
 
Posts: n/a
Default


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   Report Post  
centerNegative
 
Posts: n/a
Default


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   Report Post  
Jon Peltier
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent Chart Assistance Brent E Charts and Charting in Excel 1 May 10th 05 09:09 AM
Urgent Chart Questions Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
Urgent Chart Assistance Requested Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
Gantt chart with labels Randhir Charts and Charting in Excel 1 March 3rd 05 04:59 AM
Gantt Chart with variable width columns - is this possible? Dave Charts and Charting in Excel 0 December 13th 04 09:00 PM


All times are GMT +1. The time now is 06:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"