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

Last reviewed: February 2, 1998
Article 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:

  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

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.00 5.00a 5.00c 7.00 gant ghant xl97 97 time
Keywords : xlchart kbtool kbualink97
Version : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.