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

 

[Julie Hess]

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

Please ignore the [.B] and [./B]s in my excel code. They are text formatting to make the text bold in this forum. Do NOT include them in your formulas.