How to create a Gantt chart using hours as the scale (213915)

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.