How do I force a secondary horizontal axis to act as a date axis?

[Guest]

I have a chart with multiple data series, all using the same column of dates
for the horizontal axis. I want to set some of the series to a secondary
vertical and horizontal axis, so I can time-shift the data in these series on
the graph. However, when I put these series on a secondary axis, the
secondary horizontal axis does not provide the same options as the primary
axis (which is derived from the exact same data).

For the primary axis, under "Format Axis -> Axis Options" I am able to
select a minimum, maximum, major unit, minor unit, a dropdown of
days/months/years, etc. But for the secondary axis, under "Format Axis ->
Axis Options" I have useless options like "Interval between tick marks" and
"Interval between labels."

I thought that selecting "Date Axis" under "Axis Type" would give me the
date options, but it does not appear to have any affect.

What gives?

 

[Guest]

Upon further inspection, it appears to be a bug with a particular chart,
originally created in Excel 2003 (but saved as the new 2007 format file).
When I create a new chart from the same data, I am able to format the
secondary horizontal axis using the intended date method.

 

[Jon Peltier]

This is not necessarily a bug, just a hidden setting. For anyone who is
still reading, and not using 2007:

Go to Chart menu > Chart Options > Axes tab. Under Secondary Category (X)
Axis, choose the Time-Scale option.

This option appears in the formatting dialog in Excel 2007.

- Jon

 

[Guest]

Thanks for the reply Jon, but I'm not sure I understand what you mean by
"hidden setting." It sounds like the option you're describing on pre-2007
Excel is the equivalent of the "Axis Type" selection of "Automatically
select..." "Text axis" and "Date axis." When I modify this selection and hit
"Close," there is no discernible affect on the chart, and re-opening the
"Axis Options" window still does not provide the date controls.

Am I missing something?

 

[Jon Peltier]

By "hidden" I just meant not easy to find (in "Classic" Excel). The Chart
menu > Chart Options > Axes dialog just controls what type of axis you have.
To change the scale you then need to double click the axis to bring up the
Format Axis dialog and use the Scale tab.

In Excel 2007, when you change the axis type on the Axis Options dialog, the
controls to set the axis scale should change according to what axis type has
been selected. However, unlike in prior versions, if your category values
are not interpreted as numeric, Excel 2007 ignores your selection of a
date-scale axis and offers only the category scale options.

Check your category data. Make sure they are dates or other numerical
values.

- Jon

 

[Guest]

Thanks again for the reply, Jon.

I'm positive the data is of the proper format, since as I said in my second
post above, I created a brand-new chart based on the exact same data, and I
was able to get it working. Like I said, I think it must have been some
strange bug in the previous chart that was imported from Excel 2003.