dates start at 01 January 1900

[melo]

Hi.

I'm trying to create a line chart with the x (category) axis showin
time.

If I open Chart Options and select Automatic for the x axis, the date
start at 01 January 1900, ignoring my date data.

But if I choose Time-scale (in Chart Options), the x axis is O.K. (i
uses my values), but all my Y values vanish completely, so that th
chart is blank.

Any ideas would be appreciated.

Thanks

 

[philcud]

have you tried setting the minimum value on the scale to be the date
you want, with an automatic axis?

 

[Jon Peltier]

What dates are you using? 01 January 1900 is how Excel formats the
number 1 as a date. If you are not specifying true numerical dates,
Excel may be simply counting 1, 2, 3,... I suspect Excel thinks your
dates are just text labels without any numerical value.

I also think the behavior you're getting when you say Auto is selected,
where the dates appear starting with 1/1/1900, is what happens when
you've actually selected TimeScale.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[melo]

Philcud: I did initially try playing around with minimum values, bu
now, I can't seem to be able to locate that feature anymore!
Any hints?


Jon: the dates I'm using range from 01 September 2004 to 30 August 200
(weekdays only, so that there'd be approximately 250 values).

I am specifying Date (on the Format Axis [box] > Number [tab] ).

When I select Auto (from Chart Options > Axes), my Y values (i.e. line
on the plot area) are O.K., but the dates begin from 1/1/1900.

When I select Time-scale, my own dates (i.e. starting 1/9/2004) finall
come up on the x-axis, but all the Y-values disappear. There's nothin
left on the plot area

 

[Jon Peltier]

Are the dates left aligned in the cells, or right aligned? (Make the
cells wide enough to distinguish, and format the column to have General
horizontal alignment.) How are the cells number formatted? I think Excel
is treating them as text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jerry W. Lewis]

Use an "XY (Scatter)" chart.

In a Line chart, the (optional) x-values (if given) are considered
category labels. When numeric treatment is needed (as in fitting a
trendline, or apparently a date format) then the numeric values are
taken to be 1,2,3,... (which as dates are 1Jan1900, 2Jan1900, ...).

Jerry

 

[melo]

Hi.

Finally, after a lot of poking around, I found a fix.

I'm not sure if this is any news to anyone, but just in case there'
someone out there with the same problem, I'll describe what I did.

i) right click on the plot area and select Source Data;

ii) go down to 'Category (x) axis labels' and click the Collapse Dialo
button (at the right end of the blank slot);

iii) select the column or row in your worksheet containing the dat
data that you want displayed along the x-axis of your chart;

iv) click the Collapse Dialog button.

That should be it!

Many thanks to Philcud, Jon and Jerry for your help