Area chart date format

[Matt McQueen]

I have an area chart that plots quarterly data. I want to show only years on
the x-axis, so I changed the interval to 1 year and the number format to
'Custom yyyy'. However this is shortening my dates, so 2020 becomes 202.
Bizarrely, 2021 and 2031 show up in full, but other dates are only shown by
their first three digits.

Any ideas?

 

[Jon Peltier]

I suspect the space allotted for each label isn't quite long enough, so the
last character is truncated. I know all digits are the same width, but
sometimes, especially when the font size is small, "1" is narrower than the
rest, which explains why it isn't truncated.

Could you make the chart wider, or reduce the number of labels (increase the
major spacing)?

- Jon

 

[Matt McQueen]

Jon,

Alas it were that easy. Expanding the chart size makes no odds, and neither
does making the font smaller or rotating the text 90 degrees. Excel is
definately doing this deliberately for some reason.

What's interesting is that dates from 2011 to 2019 are all displayed
correctly. 2020 is where the problem starts. As an experiment, I extended the
date way out past my data range (up to 2080), and discovered that it's not
consistent in only displaying 01 years. After 2051, correct behaviour is
exhibited by 2055, 2057, 2061, 2071, 2075 and 2077.

Annoyingly, if I change the date to 'mm-yyyy' then it's all fine. It also
shows the right numbers with just 'yy'.

Cheers,

Matt

 

[Jon Peltier]

Bizarre. What version of Excel?

- Jon

 

[Matt McQueen]

2003 (11.6355.6360) SP1.

It's totally weird. I get the same result with the same date column but a
different set of quarterly data on the same worksheet. But if I copy the data
into a different sheet then it's all fine and dandy.

Guess I'll just work around it and notch it up to the Excel gods.

Thanks for the help.

Matt

 

[Jon Peltier]

"But if I copy the data into a different sheet then it's all fine and
dandy."

Yup. So recreate your sheet and chart, and sacrifice the old one to the
gods. Something's gotten corrupted, and before you ask what or why, just let
me say, Excel is like that sometimes (rubbing my chin philosophically).

- Jon