I have sheet from which I attempt to import some data to a graph:

enter image description here enter image description here However, as you can see, the x-axis labels are not imported either as text nor as dates, but with the first two ciphers correct, and then "yy" instead of the year. The x-axis labels are displayed with "yy" in the chart as well.

Can anyone tell me what could possibly be the issue here?

  • 1
    Please check if the number format for X-axis is correct. – Lee Sep 24 '18 at 8:34
  • @lee Thanks, having a closer look at that seems to have fixed the issue – eirikdaude Sep 25 '18 at 8:01
  • :D Glad to help you. – Lee Sep 25 '18 at 8:20

I think this has to do with different abbreviations being used for numberformats in different locales. It appears that while the numberformat dd.mm.yy was valid for range-objects, when I tried to use that range as the sourcedata for the x-axis of my chart, the numberformat got carried over where yy was not a valid abbreviation for the numbers in the chart. Because of this, it was interpreted as a string and displayed as such.

One quick fix I found was closing and opening the workbook again, which often fixed the interpretation of the numberformat.

Alternately one can go to format axis > axis options > number > type and set it to the desired format: enter image description here

If you are generating your charts using vba-code, I found that adding the line

WorkSheets(1).ChartObjects(1).Chart.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd.mm.yy"

after the data has been added to the chart, fixed the problem just fine as well (go figure).

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.