graph dates revert to 1900

  • Thread starter Thread starter Richard Goh
  • Start date Start date
R

Richard Goh

I am attempting to plot my investments value against dates with Excel 2000
in a Windows XP SP2 system
I have over 750 date rows, with gaps starting in 2003
When I choose a scatter chart, the dates on the chart revert to Jan 1, 1900
It was working up until the number of rows exceeded 699.

Is this a problem with Excel? Is there a limit to the number of rows that
can be used?
 
Richard -

A scatter chart series can have no more than 32,000 points. So that's
not it. I suspect there's a bit of nonnumeric data at about row 699 or
700. If it looks like a number but it's left-aligned in the cell, this
could be the problem. Make sure the cell has a date or general format
(not text), then copy a blank cell, select the bad cell, and use Paste
Special from the Edit menu, with the Values and Operation-Add options,
to try to fix it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
I can plot this range of data, so it is not bad data. I now have several
plots, one for each year. The problem only occurs when I choose more than
699 rows (any 699 rows).
 
Richard -

What are these gaps? Is it a formula that returns "" to look like a
blank? A chart doesn't treat "" as a blank, it treats it as text, with a
value 0. January 0, 1900 is day 0 in Excel's calendar.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
The dates are not in a 1-2-3-4-5-6-7 order, because the stock market is not
open every day. They are typically Monday through Friday and then the next
date is Monday again. Hence I use the scatter plot so that there are no
zero values for the days the stock market is not open. Again, I can get a
good plot up to, but not including 700 rows. I have a good plot from row
600 to row 743 now. I just cannot capture more than 699 consecutive rows.
 
additional information. There are no formulas in this data.
The dates are added by dragging a corner down to create consecutive dates.
Then I delete the rows that have dates when the stock market is not open.
I fill in the value at the end of each day as reported by my the brokers
web page.
I have used this data now for more than 4 years. but finally the number of
rows exceeded 699 and the plot automatically changed the dates to January
1,1900 to 1904.
If I graph any number of consecutive rows, less than 699, I get the date
printed in column one.
 
You can use a line chart, and eliminate the weekend gaps --
Select the chart, and choose Chart>Chart Options.
Select the Axes tab
For Category (X) axis, choose Category.
Click OK
 
Richard -

You have supplied no information that could explain such a problem with
the chart. There is nothing magical about the number 699. If the data is
in fact valid numerical data, this is not expected behavior.

I rarely do this anymore, but I'll offer to look at your data. Email it
to me (not to the newsgroup); remove the appropriate characters so the
messae goes through.

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

Cell A728 contains .12/29/2004 instead of 12/29/2004. The dot turns this
value into text, and interferes with a time scale axis or an XY value axis.

To find such a problem value, select the column, then use Edit > Go To
(or CTRL+G), click Special, then select the Constants option, and check
only the Text checkbox.

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