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