# Chart not reading dates correctly when date is in IF equation

1. ## Chart not reading dates correctly when date is in IF equation

First, why did I get logged out while building this post??????

I have a sheet with time series data that I want to show on a chart. But there is something strange in the neighborhood.
The sheet allows the hourly interval of the charted data to be specified, as in '3' for every 3 hours.

This is the DATE column for use in the chart x-axis. The purpose of this formula is to select rows (every (\$L\$2) row) from a column of source dates (in column A). This fills a column with a date and time for every \$L\$2 hour, and is the x-axis for the chart. The "+2" is because the data starts on row 3 in the sheet.
=INDIRECT("A"&((ROW()-1)*\$L\$2)+2) \$L\$2 is '3' in this example.

The chart using the column with this formula for the x-axis is ...
Excel Date Chart1.jpg

The reason for the line going across at Zero is due to dates after March 30 turning to 0 (1/1/1900) because there is no more date info for the above formula to use, and the chart thinks there is data at 1/1/1900.
Excel Date Chart1 DateEnd.jpg

Now, if I want to use an addition to the formula to have the non-date cells ignored, as I did with this formula,
=IF(INDIRECT("A"&((ROW()-1)*\$L\$2)+2)>0,INDIRECT("A"&((ROW()-1)*\$L\$2)+2),"")

... then the chart will not show the data unless it's axis is set to start at 0 (1/1/1900). If the chart x-axis range includes any cells after the last March 30 date (no actual source dates after March 30) then the chart fails, falling back to thinking the data starts on 1/1/1900. Notice the change is time frame on the 1900 chart. That is wrong, and is not related to what hourly interval is chosen to display.
Excel Date Chart2 Empty.jpg Excel Date Chart2b 1900.jpg

The only way I have found to get the chart to display properly is to specify the last March 30 cell (row 664) as the end of the range in the chart "Select Data" dialog. Then it displays as it should. But I need that to be a much larger range to account for other selection of how many hours to show. The last March 30 row is 664. If I specify 665 as the end of the range in the "Select Data" dialog for the chart then it no longer used to the correct dates, even though it shows the correct dates.
Excel Date Chart3 Correct.jpg

This seems like a bug to me.  Register To Reply

2. ## Re: Chart not reading dates correctly when date is in IF equation

The spreadsheet for the OP is attached...  Register To Reply

3. ## Re: Chart not reading dates correctly when date is in IF equation

Maybe it's a bug, I'm not sure, but this is how Excel has done charting for as long as I am aware.

Now, if I want to use an addition to the formula to have the non-date cells ignored, as I did with this formula, =IF(INDIRECT("A"&((ROW()-1)*\$L\$2)+2)>0,INDIRECT("A"&((ROW()-1)*\$L\$2)+2),"")
You and I and most other spreadsheet users consider null string "" as if it is equivalent to a blank cell. To Excel, however, this is a text string (of length 0, but a text string nonetheless), and whenever Excel encounters a text string anywhere in the x axis range of a scatter chart, it uses count numbers (1,2,3,4...) for the x values instead of whatever numbers are present in the range. MSFT has never provided a function that completely mimics a blank cell, though they do provide the NA() function/error that is the closest thing.

This essay goes into more detail: https://peltiertech.com/mind-the-gap...g-empty-cells/

As for it being a bug -- again I don't know. If it makes any difference to you, I can open your file in LO Calc -- which does ignore null string cells in the x axis range of a scatter chart -- and I don't get the flat line at 0 in the first chart. Whether it is a bug or not is up for debate. If you don't like the way Excel behaves, there are other spreadsheets that may provide better behavior around this issue.  Register To Reply

4. ## Re: Chart not reading dates correctly when date is in IF equation

Hi Thanks very much for pointing out NA(). That solved it.

What I did was put NA() instead of "" at the end of the IF expression for the dates column. That allows the chart to plot at the correct location. However, it still had the line at zero, so I also added NA() at the end of an IF expression for the change in volume data column, and now it works as expected.

Thanks again.  Register To Reply