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

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

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
--------------------

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

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

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.