Microsoft KB Archive/213915

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 11:51, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


How to create a Gantt chart using hours as the scale

Article ID: 213915

Article Last Modified on 1/24/2007



APPLIES TO

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition



This article was previously published under Q213915

For a Microsoft Excel 98 and Excel 97 version of this article, see 152820.

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 in Microsoft Excel, 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 shown in the following table:

   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 shown in the following table:

   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, use the steps in the following example:

  1. Start Excel and create the following 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:00 PM  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. Select cell E2.
  4. Grab the fill handle, and then fill the formula down to cell E5.
  5. On the Insert menu, click Chart.
  6. In step 1 of the Chart Wizard, click Bar under Chart type, and then click Next.
  7. On the Data Range tab, next to Series in, click Columns.
  8. Click the Series tab, and then click Add.
  9. Click the Name box, and then click cell B1 on the worksheet.
  10. Click the Values box, delete any values in the box, and then select cells B2:B5.
  11. Click Add.
  12. Click the Name box, and click cell E1 on the worksheet.
  13. Click the Values box, delete any values in the box, and then select cells E2:E5 on the worksheet.
  14. In the Category (X) axis labels box, delete any existing values, select A2:A5 on the worksheet, and then click Next.
  15. In step 3 of the Chart Wizard, click Next.
  16. In step 4 of the Chart Wizard, click As new sheet and then click Finish.
  17. On the Chart menu, click Chart Type.
  18. Under Chart sub-type, click Stacked Bar (Top row, second column), and then click OK.
  19. Select the first (Start) series on the chart.
  20. On the Format menu, click Selected Data Series.
  21. On the Patterns tab, under Border, click None.
  22. Under Area, click None, and then 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.
  23. Click the Value (y) axis.
  24. On the Format menu, click Selected Axis.
  25. Click the Scale tab, and then type following values in the listed settings.

       Axis Setting                    Value
       ------------------------------------------------
       Minimum:                        .33333
       Maximum:                        1.2
       Major Unit:                     .0834
       Minor Unit:                     .01
       "Category (X) Axis Crosses at"  .33333
                            

    Note that all the check boxes under Auto are cleared.

    Note: The Axis settings for this chart are modified for clarity, the Maximum has been set to 1.2 to allow comparison of hours that extend past midnight, and the Major Unit was doubled to reduce congestion on the chart.

  26. Click OK.

The chart 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 more information about creating charts, click Microsoft Excel Help on the Help menu, type create a chart in the Office Assistant or the Answer Wizard, and then click Search to view the topic.


Additional query words: gant ghant time XL2000 XL

Keywords: kbhowto kbualink97 KB213915