| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create dates before 1900 | Excel Worksheet Functions | |||
| how do I get excel to format dates before 1 jan 1900 | Excel Discussion (Misc queries) | |||
| Dates before 1900 | Excel Worksheet Functions | |||
| Need to sort dates before 1900 in proper order | Excel Discussion (Misc queries) | |||
| Dates prior to 1900 | Excel Worksheet Functions | |||
Linear Mode
