Date axis with a partial year

Question

I have a line chart with data spanning from February 2002 to today. At the moment, the date axis looks like this:

Is there a way to have the first date be Feb-02 (no change), but the next one be Dec-02 then Dec-03 etc.?

Answer 1

In a word - cheat. Use a 'real' date axis and make sure all your data has real dates (eg first or last of the relevant month). This should plot your chart with the first data point at Feb 02 and subsequent ones in Decembers. The spaces between points will be uneven, although the intervals on the axis will be equal (and not line up with your data points).

So scrap the useless axis, hide the axis tick marks and labels completely (ie choose "none" for primary axis).

Plot a second range of data for the same dates but with all values as zero. Show data labels on this second line, beneath the points, choose the category label rather than data values. Colour it grey and choose a narrow line width (eg 1 pt) so it does not stand out like a series. This should look very much like an axis line now. Add data point markers if you need to mimic tick marks (I would suggest the cross marker, in about 3pt).

You will need the labels to be based on cells where the format is set to "mmm yy" to show as Feb 02, Dec 02, Dec 03 etc as per your example. This could be you primary data range or a second one reformatted.

If the labels are too close to your 'axis' try resizing the plot area away from the bottom edge of the chart area. I find it sometimes gets cramped up otherwise. Once it has some breathing room it's OK. You may find that adding the series before removing the axis works better.

The method is completely flexible and will basically reflect whatever range of dates you have, with various unequal intervals, as long as you always have a second column of data with zero values for all the same dates as your actual (plotted) data.

Answer 2

That's a tough question, because Excel wants to have regular intervals for its axis spacing. See if this works for you, and if not we can try something more creative:

1. Format your X-axis as a Date Axis.
2. Set your minimum date as 12/01/2001
3. Set your maximum date as 12/01/2012
4. Set your major unit as 1 Year
5. Set your Vertical Axis crosses at date 2/1/2002-this will move the vertical axis from the left most position (12/1/2001) to 2/1/2002.
6. Set your position axis On Tick Marks
7. Save changes and see if that's what you're looking for.