+ Reply to Thread
Results 1 to 4 of 4

Chart not reading dates correctly when date is in IF equation

  1. #1
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2010
    Posts
    11

    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.
    Last edited by lahatte; 01-25-2023 at 06:20 PM.

  2. #2
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2010
    Posts
    11

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

    The spreadsheet for the OP is attached...
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,908

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2010
    Posts
    11

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Select range of dates not pulled into chart correctly
    By excelnewbie2017 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-30-2017, 11:01 AM
  2. Excel not reading in dates correctly
    By greatether in forum Excel General
    Replies: 3
    Last Post: 07-07-2014, 04:34 PM
  3. Replies: 2
    Last Post: 06-25-2014, 05:59 AM
  4. X axis Dates not appearing correctly in combined chart
    By ballstopicasso in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-02-2014, 06:49 AM
  5. Dates not reading correctly
    By TIERNAN in forum Excel General
    Replies: 6
    Last Post: 11-04-2012, 09:32 AM
  6. Chart x-axis not plotting dates correctly
    By cmyers in forum Excel General
    Replies: 3
    Last Post: 11-10-2010, 02:31 PM
  7. Reading date from a cell with series of dates
    By bruce71101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-13-2010, 03:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1