You’ve built your chart, it all makes sense and suddenly you look at your horizontal axis, and the date axis is wrong. This happens a lot. You expect to see the dates you have in the data but different dates are showing, normally the beginning of month date instead of the end of month date.
Excel Details: Excel Date And Time Axis.Excel Details: Plotting data on time (date) axis.Excel Details: For example, Excel chooses a text-based axis when dates are stored as text in a I have Excel 2013 and need to change the y-axis date format from MM/DD/YY to MM/YY.The y-axis is a date which Excel forces this date to be auto-calculated. Axis Type Excel also shows the dates between 8/24/2018 and 9/1/2018. To remove these dates, change the axis type from Date axis to Text axis.
Table of contents
YouTube Date Axis in Excel Chart is wrong
Date Axis in Excel Chart is wrong- the issue
As shown below, the data clearly shows month end dates (31 Jan 2016) but the chart is showing the beginning of the month (1st Jan 2016) on the horizontal axis of the chart. This may seem like an error but it is actually a feature. The key is to understand that if Excel sees a valid date (e.g. the 31 Jan 2016) it will open up new options in some of its tools and charts are one of them.
Excel Scatter Axis Date
If you right click on the horizontal axis and choose to Format Axis, you will see that under Axis Type it has 3 options being Automatic, text or date. As we have entered valid dates in the data the Automatic chooses dates and therefore you get the option in the second box. If Excel sees valid dates it will allow you to control the scale into days, months or years.
So if, instead of having the Base unit as Months, I change it to Days the Chart will adjust the axis to show the data points on a chart that has a daily scale. So below you can see the gaps in days between the points.
If I choose years it collapses the time period into years.
How to force Excel to use your typed in dates in a chart
Although this feature is useful, sometimes you just want Excel to show the dates you typed.
In order to do this you just need to force the horizontal axis to treat the values as text by
- right clicking on the horizontal axis,
- choose Format Axis
- Change Axis Type to be Text
Note that you immediately lose the scaling options and the date scale puts in exactly what is in the data, onto the horizontal axis.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.