Gantt chart with labels
 

Hi - I am using Excel to create gantt charts by following the very helpful
instructions at:
http://office.microsoft.com/en-us/as...346051033.aspx. I am
struggling on two parts though:

1. How do I get the completion dates to show up on the bar? Just turning on
labels shows me the number of days not the date in the above example.

2. The month/time scale does not work for me - by setting the major step to
30 or even 61, the months start off fine - with jan 1, etc, but later through
the year, it goes off due to the difference in the number of days each month
resulting in Jan 1, Feb 3, Marc 6, etc. How do I get it to consistently show
the 1st of each month.

Thanks in advance for your suggestions.

Randhir -

Here's how to achieve both effects.

Construct a line chart with X = completion date and Y = numbers 1, 2, 3, etc,
corresponding to the number of the corresponding task from the bottom. Set up the X
axis to be a time scale axis (probably default, but if not, go to Chart menu Chart
Options Axes, and check Time Scale under Category Axis) with the scale set so the
Base Unit is Day(s) and the Major Unit is 1 Month, while the Min and Max are
appropriate dates. There's the time axis formatted the way you want. Uncheck Value Y
Axis Crosses Between Dates. Format this line series to have no lines or markers
(Patterns tab), and to Show Labels as Data Labels. These are the end dates.

Then add the two series needed to construct a Gantt chart as a stacked bar chart: X
= task name, Y1 = start date, Y2 = duration in days. Selecting one series at a tine
and using Chart menu Chart Type, change these two series to stacked bars. Hide the
first (start) series by double clicking and choosing None for fill and border on the
Patterns tab.

Then the axes need some work. Chart menu Chart Options Axes tab, check the
Secondary Value Axis box. Double click the secondary category axis (with the task
names on the right), check Categories in Reverse Order, assuming you listed them in
forward order in the sheet. Double click the primary category axis (dates on the
bottom) and on the Scale tab, check Value Axis Crosses at Maximum. Double click the
secondary Y axis (dates on the top), uncheck Category Axis Crosses at Maximum, then
enter the same Min and Max dates as used for the primary date axis. Enter these as
dates, and Excel will convert them into the inscrutable whole numbers it normally
displays (today, 3/2/2005, is 38413). On the Patterns tab, check None wherever
possible to hide this axis. Double click the primary Y axis (the numbers on the
right), change the Min to 0.5 and the max to 0.5 plus the number of tasks. On the
Patterns tab, check None wherever possible to hide this axis.

That's a lot of steps, and I don't think I've left any out.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Randhir wrote:

Hi - I am using Excel to create gantt charts by following the very helpful
instructions at:
http://office.microsoft.com/en-us/as...346051033.aspx. I am
struggling on two parts though:

1. How do I get the completion dates to show up on the bar? Just turning on
labels shows me the number of days not the date in the above example.

2. The month/time scale does not work for me - by setting the major step to
30 or even 61, the months start off fine - with jan 1, etc, but later through
the year, it goes off due to the difference in the number of days each month
resulting in Jan 1, Feb 3, Marc 6, etc. How do I get it to consistently show
the 1st of each month.

Thanks in advance for your suggestions.