You are currently offline, waiting for your internet to reconnect

How to create a Gantt chart using hours as the scale

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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.
gant ghant time XL2000 XL
Properties

Article ID: 213915 - Last Review: 01/24/2007 02:58:55 - Revision: 4.2

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • kbhowto kbualink97 KB213915
Feedback