Troubles with irregular dates

[Haz]

I'm having trouble with irregularly spaced dates. Two questions:

1) How can I get my data to plot at the correct location on the x axis, that
is, between grid lines when necessary? (I have numerous gaps in my dates so
I chose the 'interpolated' option.)
and 2) Let's say I have two sets of date data, one starting in January, the
second in June. The second plot wants to start at the January grid line -
how can I get it to start at June (really same problem as #1 I guess.)

Any suggestions would be appreciated.

Haz

 

[Jon Peltier]

Excel may not recognize your dates as dates. Make sure they are numeric
dates (with general alignment assigned, they will be right aligned in the
cell). Then go to Chart menu > Chart Options > Axes tab, and select the
Time-Scale option under Category X Axis.

- Jon

 

[Haz]

Thanks Jon - but I'm not sure what you mean by 'general alignment'. My
dates are of the mm/dd/yy format, right justified. I am already in the
Time-Scale option, but my as-plotted data points are way off the correct x
axis (date) value.

Haz

 

[John Mansfield]

Try adding a helper column to your worksheet. Assuming your dates start in
cell A1 and the helper column is column B, add this formula to cell B1:

=TEXT(A1,"mm/dd/yyyy")

Copy this formula to include your X-axis range. Then, change your chart
reference for the X-axis to include column B as opposed to column A.

The formula changes the date reference to text.

 

[Jon Peltier]

This will turn the dates into text labels, resulting in equal spacing
between dates, regardless of the elapsed time between them. This is the
opposite effect than the OP wanted.

- Jon

 

[Jon Peltier]

General alignment - I mean set the cell's horizontal alignment to General,
so that you are not forcing a right alignment. Under General, text is left
aligned and numbers right aligned.

Do you want the axis to recognize times as well as dates? A line chart
cannot recognize any value smaller than an integer (i.e., an entire day).
Use an XY chart instead, with labels formatted as time or date. You can
enter axis scale parameters into the Scale dialog using date or time formats
(except in Excel 2007, where this was broken).

Give an example of how the dates are off: what are the dates in the data
range, and where do the points go instead?

- Jon