3

My Excel 2010 line chart has a secondary axis. Its horizontal date axis is incorrect. Dates should range 1/1/2013 to 12/31/2013:

Date should range 1/1/2013 to 12/31/2013

All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

1

Revised; original answer wasn't accurate.

Kevin's answer almost nailed it. Either a line chart or XY chart should work, but there appears to be a problem with the way the data range is specified for the chart. The question and comments don't describe how the range is specified, so just verify that the series data range reflects the dates column of your data as the X values.

What is happening is that without the dates specified as the X values, it is using the sequence numbers of the values. So your first date is input #1, the second is input #2, etc. Excel stores dates as a day count, starting with January 1, 1900 being day 1. When you format the axis with a date format, you get what Kevin described--sequence number 1, as a date, is 1/1/1900. Your last input is entry number 252, which corresponds with 9/8/1900.

  • No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values. – Jon Peltier Jan 12 '17 at 2:47
  • @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer. – fixer1234 Jan 12 '17 at 4:00
1

I have a possible solution for this issue. I was trying to plot dates from an Excel plugin versus values on the Excel Scatter chart and but the dates were shown 1, 2, 3, 4, 5 etc.

What I found out which cleared the issue was that this can happen if you try to plot some dates that contain the time, and some dates that don't.

For instance, plotting on axis:

01.08.2015

01.08.2015 00:30:00

Won't work, but

01.08.2015 00:30:00

01.08.2015 00:34:00

Should work.

  • Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option. – mlibby Dec 13 '18 at 12:45
0

It's hard to troubleshoot this without seeing your data sheet or where the x-axis data came from, but if you entered the data for the date column like this:

1 2 3 4

Then Excel will default to interpreting those as days since the beginning of "the era." You can cure this by entering the dates in full, like this:

1/1/2013 1/2/2013 1/3/2013 1/4/2013

Additionally, it appears that your graph is ending in August (or maybe early September). If you have fewer than 365 observations -- because, for example, holidays and weekends are excluded -- then, again, inputting the actual dates will help.

If you already have input the dates, it might be that your graph isn't using those cells as the x-axis labels. In that case, you can click on the graph, then "select data," then specify "horizontal (category) axis labels."

  • One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value. – fixer1234 Jan 5 '17 at 21:03
  • All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window. – jalea148 Jan 5 '17 at 22:18
  • The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not. – jalea148 Jan 5 '17 at 23:00
  • 1
    What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel. – Jon Peltier Jan 12 '17 at 2:48
0

Make sure that the cells you are referencing (as axis labels) are "text" category and not "date" or another category. THEN, in the horizontal axes additional formatting dialogue, click "axes options", under "axis type" make sure "Text axis" is checked. This should help.

One issue I had was I had to create new cells that were "text" from the get-go. If I changed the original cells from "date" to "text" (and even rewrote in the dates), the chart would not fix itself. BUT, once I created new "text" cells, filled them with the correct labels, and referenced to those new cells as the correct data, the axis labels changed and were correct.

0

Check that both series 1 and series 2 have horizontal (Category) Axis labels. if you've built your chart without an overall chart data range, the series have independent horizontal (category) axis labels, if one of them is blank, then the dates will be ignored and points will be treated as sequential from 01/01/1900. To check, in the Select Data Source dialogue for your chart, click on series 1 in the legend Entries (Series) on the left then click the Edit button on the Horizontal (Category) Axis labels (on the right), repeat (and correct) for each Legend Entries (Series).

0

For me the answer was very simple. One of the cells in the range that contained the dates, contained some text instead of a valid date. It was the last cell. My date range was too long and it included the last cell which had text in it. I reduced the length of the range by one cell to exclude the cell containing the text and the problem disappeared. My series definition was: =SERIES(ActualData!$E$3,ActualData!$A$4:$A$20,ActualData!$E$4:$E$20,1)

So I edited it to read this, and it fixed the problem =SERIES(ActualData!$E$3,ActualData!$A$4:$A$19,ActualData!$E$4:$E$19,1)

NOTE that all of my date cells were formatted as Date (as they should be) not Text.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.