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

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

   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.

1. 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.)
   
   

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

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

4. Create the chart by using the following steps:

   Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition:

          a. On the Insert menu, click Chart.
   

          b. In step 1 of the Chart Wizard, click Bar Chart and click Next.
   

          c. Click the Data Range tab and click Series in Columns.
   

          d. 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.
   
          e. 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.
   
          f. In the Category (X) Axis Labels box, delete any existing values,
             select A2:A5 on the worksheet, and click Next.
   
          g. In step 3 of the Chart Wizard, click Next.
   
          h. In step 4 of the Chart Wizard, click As New Sheet and click
             Finish.
   
      Microsoft Excel 5.0 or 7.0:
   
          a. Select cells A1:B5. Press CTRL and select cells E1:E5.
   
          b. On the Insert menu, point to Chart, and then click As New Sheet.
             In step 1 of the Chart Wizard, click Next.
   
          c. In step 2, click Bar, and then click Next.
   
          d. In step 3, click chart type 1, and then click Next.
   
          e. 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.
   
   

5. 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 chart 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.

6. 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 place holder that places the second series in the correct position on the chart.

7. 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. (see the "More Information" section for an explanation
      of these values.)
   
      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:

   ARTICLE-ID: Q73281
   TITLE     : 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:

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office
               Program or Component

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:

   ARTICLE-ID: Q120802
   TITLE     : 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

Microsoft Excel 5.0

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

   charts