Microsoft KB Archive/213915

= 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:   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.)  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. Select cell E2. Grab the fill handle, and then fill the formula down to cell E5.</li> On the Insert menu, click Chart.</li> In step 1 of the Chart Wizard, click Bar under Chart type, and then click Next.</li> On the Data Range tab, next to Series in, click Columns.</li> Click the Series tab, and then click Add.</li> Click the Name box, and then click cell B1 on the worksheet.</li> Click the Values box, delete any values in the box, and then select cells B2:B5.</li> Click Add.</li> Click the Name box, and click cell E1 on the worksheet.</li> Click the Values box, delete any values in the box, and then select cells E2:E5 on the worksheet.</li> In the Category (X) axis labels box, delete any existing values, select A2:A5 on the worksheet, and then click Next.</li> In step 3 of the Chart Wizard, click Next.</li> In step 4 of the Chart Wizard, click As new sheet and then click Finish.</li> On the Chart menu, click Chart Type.</li> Under Chart sub-type, click Stacked Bar (Top row, second column), and then click OK.</li> Select the first (Start) series on the chart.</li> On the Format menu, click Selected Data Series.</li> <li>On the Patterns tab, under Border, click None.</li> <li>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.</li> <li>Click the Value (y) axis.</li> <li>On the Format menu, click Selected Axis.</li> <li> Click the Scale tab, and then type following values in the listed settings. <pre class="fixed_text">  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. </li> <li>Click OK.</li></ol>

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.

<div class="references_section">