Argthtjtr

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:

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.

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.

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.

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

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.

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

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.