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