How to get date formatting of x-axis labels in scatter graph

Question

I've got a data set of x and y coordinate values that I'm plotting as a scatter graph:

The horizontal axis data are date/time data, all in December 2017.  Unfortunately, the tick labels along the horizontal axis are shown incorrectly for the data.

It looks like the data is simply the index number (1,2,3,etc.), interpretted as days, and referenced to a zero-day at the start of 1900.  I recall that this is how raw numbers are converted into dates, but x-axis data is already data/time data, so whatever its internal representation with respect to the start of 1900, it should be correct for December 2017.  For this reason, I suspect that the "Horizontal (Category) Axis Labels" data shown in the image above is being ignored, and the index number is being used.

As shown above, I also looked at the formatting of the horizontal axis, but it too is set to date:

What am I doing wrong?

Answer 1

I found the problem.  The "Horizontal (Category) Axis Labels" contains only the labels, not the data for the horizontal axis coordinates.  Each series (of which there is only one above) has horizontal axis coordinate data and vertical axis coordinate data (x and y), and the range specified for this data should *not* include the data column heading.  Even if you initially select two columns of data before inserting a scatter graph, Excel will exclude that headings from the series data, and adjust the range accordingly.  In my case, I'm guessing that the heading caused Excel to not know that the plotted data (in particular, the y-series data) was date/time data.  After adjusting the ranges to exclude the column heading, all was well, and the dates showed up on the horizontal axis labels as dates.

Answer 2

Hi Franky,

Glad to know that you have sorted out the issue. 

We appreciate you sharing with us how you resolved the issue you were experiencing.

We're just a post away if you have further concerns.