Gantt Charts
Michael Ringler
I am trying to create a visual Gantt type chart with some data that is
output from a Com Port Server. The data are in time formats as shown
below:
Date Time IN Time Out Elapsed Port#
1/4/97 00:03:11 00:04:05 0:00:54 11
1/4/97 00:03:50 00:04:50 0:01:00 5
1/4/97 00:04:00 01:00:00 0:56:00 1
I would like to use Excell to generate a chart that has the various
Port#'s on the Y axis and the time throughout a twenty-four hour period
in the day on the X axis and highlighted the periods that a given port
is loaded. This will give me a visual of how the ports are loaded over
the day as well as show peak periods, capacity loading, how many ports
are up at any given time throughout the day, etc.
Any assistance will be appreciated.
Michael Ringler
mike.r...@intellink.net
George K. Atkins
Hi, Michael!
Michael Ringler <mike.r...@intellink.net> wrote in article
<32DC06...@intellink.net>...
Here is a MS knowledebase article on creating GANNT charts in Excel. You
may be able to use this to help create your own solution.
--
George K. Atkins
Iota Development Corporation
g...@iotadev.com
PSS ID Number: Q73281
Article last modified on 07-18-1996
PSS database name: EXCEL
2.x 3.x 4.x 5.00 5.00c 7.00 | 3.00 4.00 5.00 5.00a
WINDOWS | MACINTOSH
--------------------------------------------------------------------
The information in this article applies to:
- Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
- Microsoft Excel for Windows 95, version 7.0
- Microsoft Excel for the Macintosh, versions 3.x, 4.x, 5.0, 5.0a
--------------------------------------------------------------------
SUMMARY
=======
You can use Microsoft Excel to create Gantt Charts showing task status for
project planning and control. This article gives an example and shows the
steps necessary to generate the charts.
Example
-------
1. Open a new worksheet in Microsoft Excel and enter the following
values:
A1: B1: START C1: DAYS D1: DAYS
A2: TASK B2: DATE C2: COMPLETED D2: REMAINING
A3: TASK-1 B3: 1/1/91 C3: 150 D3: 15
A4: TASK-2 B4: 5/1/91 C4: 21 D4: 31
A5: TASK-3 B5: 7/1/91 C5: 0 D5: 114
A6: TASK-4 B6: 10/1/91 C6: 0 D6: 4
A7: TASK-5 B7: 10/15/91 C7: 0 D7: 31
A8: TASK-6 B8: 11/1/91 C8: 0 D8: 2
2. Select cell B2 and format with the month/day/year format you want
to use for the chart.
3. For versions 2.x, 3.0, and 4.0 only:
Highlight A2:D8 and press F11 to create a chart.
For version 5.0 and 7.0:
Highlight A2:D8 and from the Insert menu select Chart - As New Sheet.
(This starts the Chart Wizard.)
In step 1: choose Next
step 2: choose Bar
step 3: choose Bar chart #3,
step 4: change First Column for Category X-Axis labels to "1",
step 5: Finish.
4. For versions 2.x, 3.0, and 4.0:
From the Gallery menu, choose Bar and select type number 3. Bar chart 3
should be a stacked Bar chart.
5. For versions 2.x, 3.0, and 4.0:
The first data series in a chart is usually red. Select this series
and choose Patterns from the Format menu. Set the patterns for both
the border and area to None.
For version 5.0 and 7.0:
The first data series is usually blue. Select the first series and
from the Format menu choose Selected Data Series (or double-click on
the first series). Choose the Patterns Tab and set both the Border
and Area to none.
6. For versions 2.x, 3.0, and 4.0:
Select the Y-axis (horizontal axis) and choose Scale from the
Format menu.
For version 5.0 and 7.0: Select the Y-axis (horizontal axis) and from
the Format
menu choose Selected Axis then select the Scale tab.
If you are using Microsoft Excel for Windows or OS/2, set the minimum
to 33200, maximum to 33600, major unit to 31, and minor unit to 1. If
you are using Microsoft Excel for the Macintosh, set the minimum to
31777, maximum to 32120, major unit to 31, and minor unit to 1. The
minimum and maximum numbers represent serial numbers for dates. The
minimum serial number should be a date somewhat near the minimum date
in column B of the original data.
Note that in a bar chart the traditional X- and Y-axes are reversed;
the X-axis is vertical and the Y-axis is horizontal.
7. For versions 2.x, 3.0, and 4.0:
Select the X-axis (vertical axis) and choose Scale from the Format
menu.
For version 5.0 and 7.0: Select the X-axis and choose the Selected Axis
from the Format menu. Select the Scale tab.
Verify that the Value Y-Axis Crosses Between Categories check box and
that the Categories in Reverse Order check box is selected.
8. For versions 2.x, 3.0, and 4.0:
From the Chart menu, choose Gridlines and verify that the Major
Gridlines check box is selected for the Y-axis.
For version 5.0 and 7.0:
From the Insert menu choose Gridlines and verify that the Major
Gridlines check box is selected for the Y-axis.
The Gantt Chart should now be displayed on the screen. Additional
formatting can be added as needed.
REFERENCES
==========
"User's Guide," version 5.0, chapters 15, 17, 18
"User's Guide 1," version 4.0, chapter 14
"User's Guide 2," version 4.0, chapter 7
"User's Guide," version 3.0, chapters 13 and 19
"PC Magazine," Feb 26,1991, "Spreadsheets," page 410, Craig Stinson
KBCategory: kbusage
KBSubcategory: kbprb
Additional reference words: 7.00 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00 4.0a 4.00a 5.0 5.00 Gant Gannt
============================================================================
=
Copyright Microsoft Corporation 1996.
Eur van Andel
Michael Ringler wrote:
> I am trying to create a visual Gantt type chart with some data that is
> output from a Com Port Server. The data are in time formats as shown
> below:
>
> Date Time IN Time Out Elapsed Port#
> 1/4/97 00:03:11 00:04:05 0:00:54 11
> 1/4/97 00:03:50 00:04:50 0:01:00 5
> 1/4/97 00:04:00 01:00:00 0:56:00 1
>
> I would like to use Excell to generate a chart that has the various
> Port#'s on the Y axis and the time throughout a twenty-four hour period
> in the day on the X axis and highlighted the periods that a given port
> is loaded. This will give me a visual of how the ports are loaded over
> the day as well as show peak periods, capacity loading, how many ports
> are up at any given time throughout the day, etc.
I've searched the Net in vain for this and finally created it myself
The trick is that you create a stacked column chart with the categories
on the y-axis.
Make two series: one to the first time, the second for the duration.
Make the first series invisible.
Ou of sheer frustrtion over MS Project, I created a very nice (IMSNHO)
Gantt template with automatic start dates, predessessors (even several),
duration times and end dates. Mail me if interested: I'll mail you the
attached sheet. No nice red series for critiocal path, though. I'm still
working on that :-)
--
Ir. E.E. van Andel, Fine Wire Heat Exchanger Technology
Kuipersdijk 194 AB23, NL-7512 CM Enschede, The Netherlands
tel +31-53-4328568 fax +31-53-4315985
mobile +31-6-53-286573 email e...@xs4all.nl