I cannot set the maximum date for the axis of a line chart

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have a line chart with a (horizontal) date axis along the bottom.
Currently I have data from Sep-2002 to Sep-2009. All of this data is shown
on the chart and the axes also go from Sep-2002 to Sep-2009.

My problem is that I want to set the maximum of the date axis to an earlier
date (say Jun-2008) hence hiding the later part of the data (without deleting
the original data). But Excel 2007 will not let me do this (apparently).

I have selected the axes and fixed the "maximum" date at an earlier date
(like Jun-2008). Surprisingly, Excel leaves the axis exactly the same (it
still ends at Sep-09) but all the date labels beyond Jun-2008 have
disappeared. Weird!

The other weird thing is that if I try and fix the "minimum" date then it
works as I want it to. For example, I can fix the "minimum" to Sep-03 and
Excel will hide the first year of data. But for some reason it won't give me
the same level of control over the maximum!?!

Any suggestions?

Usually I can find a work-around for these sorts of things, but I am really
stumped so would appreciate insight from the experts.

p.s. I should note that I am using a line graph with a secondary vertical
axis. I think this may be part of the problem. It seems that if I eliminate
the data on the secondary vertical axis then Excel gives me back control over
"maximum" dates.
 
When I first started using Excel 2007, I had the same problem: could not
type a date into the boxes setting the axis scale. The solution is to type
the serial number of the date. On the worksheet enter the required date in
the normal way; format the cell General so that you see a 5-digit number
like 40123. Enter this value into the Format Axis box.
I do not use dates that much but I now find that I can enter a date into the
scale boxes. I cannot recall when this changed. Do you have the second
service pack (SP2) installed?
best wishes
 
You could always use dates in a line chart's dialog, but never in an XY
chart's (still can't in 2007 SP2). Microsoft did finally enable date
entry in XY chart axis dialogs in 2010.

I don't know what is the user's problem, but installing SP2 often fixes
things like this.

- Jon
 
Thanks for the replies guys.

I do have SP2 installed for Office 2007. I am also able to enter in dates -
as I mentioned, I am able to set the minimum date to whatever I want. But
not the maximum date. Note that I have also used the 5-digit number approach
and I still can't set the maximum date.

By the way Jon, your website is an excellent resource of charting tips and
techniques. Whenever I need to produce a tricky graph I go to your website
first. I want to thank you for creating it.

Cheers
David
 
I have done some further exploration of my problem. I think this may just be
a glitch with secondary axes line charts in Excel 2007.

I can show this using some "made-up" data. I entered the following data
into Excel 2007:

Date Series 1 Series 2
1 43 10
2 45 11
3 45 14
4 67 16
5 89 13
6 90 #N/A
7 89 #N/A
8 88 #N/A

I then used this data to create line chart. I made the axis a date axis.
And I put the second series on the second vertical axis.

I then changed the maximum date to "5" and Excel 2007 merely removes the
labels. It does not actually change the maximum date!

I note that if I just create the chart with Series 1 on it then I can change
the maximum date.

So it looks to me like it is a glitch! Or am I doing something wrong? Its
quite frustrating because I often use graphs with secondary axes.
 
Using two variations, without a secondary X axis and with a secondary X
axis, I was able to change the maximum by selecting Fixed and entering
1/5/1900. Entering just 5 does not work in 2007, because they forgot
that even in axis scale input boxes, numbers can have different formats.

Make sure you have SP2 installed. The original release of 2007 had lots
of chart glitches, SP1 solved many, SP2 solved many, and many remain.

- Jon
 
Back
Top