Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Richard Goh
 
Posts: n/a
Default graph dates revert to 1900

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?


  #2   Report Post  
Jon Peltier
 
Posts: n/a
Default

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

Richard Goh wrote:

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?


  #3   Report Post  
Richard Goh
 
Posts: n/a
Default

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).

"Jon Peltier" wrote in message
...
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/
_______

Richard Goh wrote:

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?



  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

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

Richard Goh wrote:
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).

"Jon Peltier" wrote in message
...

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

Richard Goh wrote:


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?




  #5   Report Post  
Richard Goh
 
Posts: n/a
Default

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.

"Jon Peltier" wrote in message
...
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/
_______

Richard Goh wrote:
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).

"Jon Peltier" wrote in message
...

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

Richard Goh wrote:


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?






  #6   Report Post  
Richard Goh
 
Posts: n/a
Default

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.

"Jon Peltier" wrote in message
...
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/
_______

Richard Goh wrote:
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).

"Jon Peltier" wrote in message
...

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

Richard Goh wrote:


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?





  #7   Report Post  
Barb Reinhardt
 
Posts: n/a
Default

Can you copy your date data here? We may see something you didn't see.

"Richard Goh" wrote in message
...
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.

"Jon Peltier" wrote in message
...
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/
_______

Richard Goh wrote:
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).

"Jon Peltier" wrote in message
...

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

Richard Goh wrote:


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?






  #8   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

You can use a line chart, and eliminate the weekend gaps --
Select the chart, and choose ChartChart Options.
Select the Axes tab
For Category (X) axis, choose Category.
Click OK

Richard Goh wrote:
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.

"Jon Peltier" wrote in message
...

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

Richard Goh wrote:

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).

"Jon Peltier" wrote in message
. ..


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

Richard Goh wrote:



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?





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #9   Report Post  
Jon Peltier
 
Posts: n/a
Default

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

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.

"Jon Peltier" wrote in message
...

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

Richard Goh wrote:

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).

"Jon Peltier" wrote in message
. ..


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

Richard Goh wrote:



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?




  #10   Report Post  
Jon Peltier
 
Posts: n/a
Default

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

Jon Peltier wrote:
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 Goh wrote:

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.

"Jon Peltier" wrote in message
...

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

Richard Goh wrote:

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).

"Jon Peltier" wrote in message
...


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

Richard Goh wrote:



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?







  #11   Report Post  
Richard Goh
 
Posts: n/a
Default

Many thanks. I missed that little detail. Now the graph works again.

"Jon Peltier" wrote in message
...
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/
_______

Jon Peltier wrote:
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 Goh wrote:

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.

"Jon Peltier" wrote in message
...

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

Richard Goh wrote:

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).

"Jon Peltier" wrote in message
...


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

Richard Goh wrote:



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?







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to create dates before 1900 Pieter Excel Worksheet Functions 2 February 3rd 05 10:06 PM
how do I get excel to format dates before 1 jan 1900 keithcarteruk Excel Discussion (Misc queries) 1 February 3rd 05 06:23 PM
Dates before 1900 David Cleave Excel Worksheet Functions 1 January 18th 05 01:37 PM
Need to sort dates before 1900 in proper order sandage_2000 Excel Discussion (Misc queries) 3 January 8th 05 04:31 AM
Dates prior to 1900 Kenn Tews Excel Worksheet Functions 0 November 22nd 04 03:49 AM


All times are GMT +1. The time now is 10:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"