dates start at 01 January 1900

Hi.

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

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

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

Any ideas would be appreciated.

Thanks.

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

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 wrote:

> Hi.
>
> I'm trying to create a line chart with the x (category) axis showing
> time.
>
> If I open Chart Options and select Automatic for the x axis, the dates
> start at 01 January 1900, ignoring my date data.
>
> But if I choose Time-scale (in Chart Options), the x axis is O.K. (it
> uses my values), but all my Y values vanish completely, so that the
> chart is blank.
>
> Any ideas would be appreciated.
>
> Thanks.
>
>

Philcud: I did initially try playing around with minimum values, but 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 2005 (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. lines 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) finally come up on the x-axis, but all the Y-values disappear. There's nothing left on the plot area.

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/
_______


melo wrote:

> Philcud: I did initially try playing around with minimum values, but
> 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 2005
> (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. lines
> on the plot area) are O.K., but the dates begin f rom111900.
>
> When I select Time-scale, my own dates (i.e. starting 1/9/2004) finally
> come up on the x-axis, but all the Y-values disappear. There's nothing
> left on the plot area.
>
>

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 wrote:

> Philcud: I did initially try playing around with minimum values, but
> 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 2005
> (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. lines
> 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) finally
> come up on the x-axis, but all the Y-values disappear. There's nothing
> left on the plot area.

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's 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 Dialog button (at the right end of the blank slot);

iii) select the column or row in your worksheet containing the date 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.