Value Axis Adjustment

[Workbook]

I created a Gnatt Chart with 8 activities. The times on the values axis are
listed as 3:50 AM, 4:19 AM, 5:16 AM, 6:14 AM, 6:43 AM, 7:12 AM, 7:40 AM, 8:09
AM, 8:38 AM, however since my activities take place between 5:30 AM and 8:15
AM, I wanted to decrease the time that is displayed on the chart
dramatically. For example, I would like to see the time on the values axis
displayed in 5, 10, or 15 minute increments. I am assuming this requires me
to adjust the minimum, maximum, major unit, minor unit, and category X axis
data in the Format Axis, but I am not sure what to enter. Could you help me
out with this

 

[Jon Peltier]

If you are using Excel 2003 or earlier, you can simply enter the times for
axis scale parameters. For example, min = 5:30 AM, max = 8:30 AM, major unit
= 0:30.

If you are using Excel 2007, you can't do this, because Microsoft broke this
functionality. You will have to figure out the numerical equivalents of
these times. For example, 5:30 = 5.5/24 or 0.2291666, 8:30 = 8.4/24 or
0.3541666, and 30 minutes = 0.5/24 or 0.0208333.

- Jon

 

[Workbook]

Hi Jon,

I have the following data in the scale tab of the format axis (found below),
which is what you suggested with one exception being the major /minor unit.
You're recommendation is perfect, exactly what I was trying to do! Thank you
for your help. However now I am having a different problem. The actual bars
on the graph have disappeared when I change the data to what I have below
(same thing happens when the major/minor unit is 30 min). Do you have any
idea of what might cause that to be happening?

Minimum: .02291666 = 5:30 am
Maximum: 0.3541666 = 8:30 am
Major Unit: 0.00347222222 = 5 minutes
Minor Unit: 0.00347222222 = 5 minutes
Category X axis crosses at: 0.2291666 = 5:30 am

Thank you for your time John, I appreciate your feedback.
Ed

 

[Jon Peltier]

What were the axis scale settings before you changed them? If the numbers
had dates as well as times, then they would not be between 0 and 1.

- Jon

 

[Workbook]

Hi Jon,

The axis scale settings, before I changed them, were as follows.
Minimum = 1.16
Maximum = 1.36
Major unit = .02
Minor unit = .004
Category X axis crosses at = 1.16

You had said "If the numbers had dates as well as times, then they would not
be between 0 and 1." Could you help me to understand better what you mean?

Ed

 

[Jon Peltier]

Excel treats dates as the whole number of days since 1/1/1900 (so now we're
somewhere between 39000 and 40000). Excel treats times as a fraction of a
day between one midnight to the next. The original values you just posted
are greater than 1. Probably not intended as a date (2-Jan-1900), but some
other artifact of the recording process.

The values you started with are between 1 and 2. The times that I gave you
and that you applied have values between 0 and 1. Add 1 to make the new
values fit your data.

- Jon