Date Axis in Excel Chart is wrong

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.

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.

Date Axis in Excel Chart is wrong

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.

Date Axis in Excel Chart is wrong

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.

Date Axis in Excel Chart is wrong

If I choose years it collapses the time period into years.

Date Axis in Excel Chart is wrong

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.

Excel waterfall chart template

Fill a date series in Excel (even if only workdays)

Why is excel always 1 day short when subtracting dates