Dates Converting to 1900 When Used in Chart?

[Alkemist]

Hia everyone...

I've been experimenting with dynamic charts by defining a formula using the COUNTA function etc...

One column of data that is used in my charts are dates, they form the x axis and start at about April 2000 going through to the present day (format used xx/xx/xxxx). The column is formatted as a date column.

On the charts which use this data the dates either do not appear at all (using auto units on the x axis) or start from 1900 if the x axis is set to date units. It is doubly strange because the data covers almost 4 years from April 2000 to Dec 2003 but the x axis starts from 1900 and goes up to about 1908!?!?!? The charts are displayed correctly despite the incorrect x axis. If I manually change the x axis range to cover my data range the chart gets squashed to the left as if it's still trying to plot between 1900 and 1908.

Before when I altered the charts manually (no dynamic charts) by clicking on the chart and dragging the box to cover the data the x axis displayed normally.

What am I doing wrong and how do I put it right?...I'm well confused and have spent many hours trying to figure it out...

Thanks in advance...Alkemist

 

[GlennUK]

Your dates are not dates but string entries. Convert them to be actual Excel dates and everything should work fine.

One way to convert string entries to numeric is to type 1 in an empty cell, copy that cell, and paste it over the cells you want to convert using Edit/Paste Special/Multiply.

You may need to format the cells as date again afterwards.


Cheers, Glenn.

 

[Jon Peltier]

Why get the blank cell dirty by typing a 1? Copy a blank cell, and use paste special - values - add.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[Alkemist]

Thanks guys...I knew it was something simple but couldn't quite figure out what it was....Not likely to forget it again though!...Alkemist

 

[pobrien]

A colleague is using Excel 2010 and having a similar problem. He has his X-axis values in a column. They are dates and formatted as such (x/xx/xxxx). Both in the chart and in the column the format is set to Date, but the chart automatically assumes they are numbers and converts them so that the x-axis values are all in the year 1900 instead of 2010. We tried the Paste Special - Value - Add from a blank cell and from a cell with a 1 in it, but that did not solve the problem. Any other ideas?

Thanks!

 

[jbw55]

I have the same problem in Excel 2010. The dates were displaying correctly until I associated one of my 3 data series with a secondary axis, at which time the dates show as starting at 1/1/1900. I have been using Excel for over 20 years and have never had this kind of problem before on any of the previous versions of Excel.

Any assistance would be greatly appreciated.

 

[Harleygal14]

I have two different charts that are using the same column for the dates. One works perfect and the other one is displaying the dates as 1/1/1900, 1/2/1900, etc. The correct one is showing the actual dates that appear in the column that are generally Friday of each week but some may be Wednesday or Thursday of a given week. How can it work for one chart and not the next in the same Workbook using the same column data? I also am using Excel 10

 

[tedtimmons]

I too am having this problem.
My source data type is date but still appears on the x axis as 1/0/1900, 4/9/1900, 7/18/1900, etc.
Chart type is X Y (Scatter)
Excel 2010, Windows Vista, I have other similar charts (different workbooks) that display as would be expected.
Can't see what's different with this one

 

[tedtimmons]

I found my problem
I had ONE cell out of 700 that wasn't a valid date.
Once I fixed that one cell, the dates appeared on the chart as expected.

 

[Julie Hess]

I found my problem
I had ONE cell out of 700 that wasn't a valid date.
Once I fixed that one cell, the dates appeared on the chart as expected.

This solution still works in 2024.

My excel formulas were checking the first date in the list, and adding a half day, and then if the new date is in the same month as the old date, replacing it with a blank "". It was the "" cells which were messing with the X axis. When the formula incremented high enough such that it rolled over to the next month and the date began to read as "", the X axis failed and started listing 1/0/1900 as the start date. By replacing all of the "" cells with NA() instead, the issue resolved itself. See the formulas below.

Formula which resulted in X axis failing to 1/0/1900:

=IFERROR(IF(MONTH(I3+0.5)=MONTH(I3),I3+0.5,[B]""[/B]),[B]""[/B])

Note that this formula was in cell I4 copied through I64, and the I3 referenced here was an absolute date between 1/1/24 and 1/31/24.

Fixed Formula:

=IFERROR(IF(MONTH(I3+0.5)=MONTH(I3),I3+0.5,[B]NA()[/B]),[B]NA()[/B])