XL: How to Create a Gantt Chart Using Hours as the Scale

ID: Q152820

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

SUMMARY

You can use a Gantt Chart to show the progress of a project or the relationship among the tasks in a project in relation to time. The unit of time that is most frequently used is the day unit. However, you can also use the hour as the unit of time to show the status of the task. To do this, alter the scale of the value (y) axis.

This article contains an example that uses the hour as the unit of time to show the status of a task.

MORE INFORMATION

The scale in a normal Gantt Chart is based on the serial number of the date. The scale used for the value (y) axis is as follows.


   Axis Setting                   Value or definition
------------------------------------------------------------------------

   Minimum                        Serial number of earliest date or some
                                  date before the earliest date.
   Maximum                        Serial number of latest date or a date
                                  greater than the latest date.
   Major Unit                     31, or one month.
   Minor Unit                     1, or one day.
   "Category(X) Axis Crosses at"  Same as the Minimum setting.

Time is entered as a fraction or decimal representation of part of a single day. The scale used for a Gantt Chart that is based on hours is as follows.


   Axis Setting                    Value or definition
------------------------------------------------------------------------

   Minimum                         The decimal number representing the
                                   earliest hour charted.
   Maximum                         1.0, or a single day.
   Major Unit                      .0417, or the decimal equivalent of 1
                                   hour.
   Minor Unit                      .000694, or the decimal equivalent of 1
                                   minute, or .01 (if minutes are not a
                                   factor).

   "Category (X) Axis Crosses at"  Same as Minimum.

To alter the scale, follow these steps:

Type the following data into a worksheet:


      A1: Depts  B1: Start    C1: End      D1: Duration1  E1: Duration2
      A2: NC     B2: 8:30 AM  C2: 5:00 PM  D2: 8.5        E2:
      A3: TX     B3: 8:30 AM  C3: 5:30 PM  D3: 9          E3:
      A4: AZ     B4: 3:00 PM  C4: 12:00PM  D4: 9          E4:
      A5: WA     B5: 8:30 AM  C5: 6:30 PM  D5: 10         E5:

This information shows the times at which four sites across the country start a work day in relation to the other sites. (The times are all entered as Eastern Standard time.)

In cell E2, type the following formula to convert the duration, which is expressed in hours, to a decimal fraction of a single day:

=D2*0.0417

NOTE: The value 0.0417 is the decimal equivalent of one hour.

With cell E2 selected, use the fill handle to fill the formula down to cell E5.

Create the chart by following these steps:

Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition:
1. On the Insert menu, click Chart.
2. In step 1 of the Chart Wizard, click Bar under Chart Type and click Next.
3. Click the Data Range tab and click Columns under Series In.
4. Click the Series tab and click Remove to clear the Series box. Add the first series by clicking Add. Click the Name box and click cell B1 on the worksheet. Click the Values box, delete any values in the box, and then select cells B2:B5.
5. Click Add, click the Name box, and click cell E1 on the worksheet. Click the Values box, delete any values in the box, and then select cells E2:E5 on the worksheet.
6. In the Category (X) Axis Labels box, delete any existing values, select A2:A5 on the worksheet, and click Next.
7. In step 3 of the Chart Wizard, click Next.
8. In step 4 of the Chart Wizard, click As New Sheet and click Finish.
Microsoft Excel 5.0 or 7.0:
1. Select cells A1:B5. Press CTRL and select cells E1:E5.
2. On the Insert menu, point to Chart, and then click As New Sheet. In step 1 of the Chart Wizard, click Next.
3. In step 2, click Bar, and then click Next.
4. In step 3, click chart type 1, and then click Next.
5. In step 4, click Data Series in Columns, click Use First 1 Column(s) as Category (X) Axis Labels, click Use First 1 Row(s) for Legend Text, and then click Finish.

In Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition, with the Chart sheet active, click Chart Type on the Chart menu. Click the second item under Chart Sub-type, the stacked bar, and click OK.

In Microsoft Excel 5.0 or 7.0, with the Chart sheet active, click Chart Type on the Format menu. In the Chart Type dialog box, click Options. This step opens the Format Bar Group dialog box. Click the Subtype tab, click the middle option (the stacked bar), and then click OK.

Select the first series on the chart and click Selected Data Series on the Format menu. Click the Patterns tab, change the Border and Area to None, and click OK.

The series becomes transparent.

NOTE: In this example, the first series is a placeholder that places the second series in the correct position on the chart.

Next, change the scale of the value (y) axis to reflect the use of hours instead of days. To do this, select the value (y) axis and click Selected Axis on the Format menu. Click the Scale tab, type following values for the following settings.
```
      Axis Setting                    Value

      ------------------------------------------------

      Minimum:                        .33333 (8:00 am)
      Maximum:                        1.0
      Major Unit:                     .0417
      Minor Unit:                     .01
      "Category (X) Axis Crosses at"  .33333 
```
Then, click OK.

The chart that results displays the duration of each shift as a floating bar. The left edge of each bar is the starting time, and the right edge is the ending time. The relative positions of the bars show the relationships among the shifts.

REFERENCES

For additional information about creating Gantt Charts, please see the following article in the Microsoft Knowledge Base:

Q73281 : Excel: Creating Gantt Charts

Microsoft Excel 98 Macintosh Edition

For more information about charting, click the Office Assistant, type "charting," click Search, and then click to view "Create a chart."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

Q179216 : OFF98: How to Use the Microsoft Office Installer Program

Microsoft Excel 97

For more information about charting, click the Office Assistant, type "charting," click Search, and then click to view "How Microsoft Excel represents worksheet data in a chart."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

Q120802 : Office: How to Add/Remove a Single Office Program or Component

Microsoft Excel 7.0

For more information about charts, click Answer Wizard on the Help menu and type:


   charts

then double click on the "How worksheet data is represented in a chart" topic.

Microsoft Excel 5.0

For more information about charts, click on the Help menu and choose Search for Help on, then type:


   charts

then double click on the "Tips for Working with Charts" topic.

Additional query words: 5.00a 5.00c gant ghant xl97 97 time


Keywords          : kbtool kbualink97 xlchart 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,98
Platform          : MACINTOSH WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 22, 1999