Excel chart - Changing Axis Format number/format to 01-mmm-yy

[jaspalsd]

Hi,

I am wondering if it's possible to change the axis labels of my date range from MMM-YY to the date of 01-MMM-YY ?

This is because I have gridlines and as an example one of my bars starts on the 1st July 2019 but my bar will start before the gridline of "Jul-19" because the axis date is 04-07-19 without formatting because of my 30.0 intervals.

It is not big deal and I can just remove the gridlines but it'll be handy to know

thanks for your suggestions!

 

[Gerald Higgins]

Yes this is do-able.

Right click on the axis.
Format Axis
Number
Under Category, select Custom
Then in Format Code, type in something like dd-mmm-yy
Then click Add.

 

[jaspalsd]

Hi Gerald,

I've already done this but I need it to always start on the first of each month.

E.g. 01-MM-yyyy

one of my examples is that the bar starts from 01-07-2019 but the gridline starts at 04-07-2019

 

[Gerald Higgins]

So let's see if I've got this right, you don't actually want to change the axis labels from MMM-YY to 01-MMM-YY, you actually want to force the gridline to be on the 1st day of the month, is that right ?

In that case, try this . . .

Right click on the chart axis
Format axis
Under Axis Type, choose Date Axis
On Major Unit, select the option for Fixed.
Also on Major Unit, select the drop down box on the right (it may say "Days") and choose Months.
In the box to the left, it may have the number 7, change this to 1.
Then on Minimum, choose Fixed, and select a date that is the first day of your first month.

 

[jaspalsd]

. .

Right click on the chart axis
Format axis
Under Axis Type, choose Date Axis

Thanks for the advice. Unfortunately for my chart the "Axis Type" is not available because it's most likely that I have stacked bars. I did a test on a column chart with simple data and the axis type was available.....

Screenshots attached

Thanks for the tips, I'm sure it'll be useful for me in the future

 

[jaspalsd]

I am an idiot (edited) after realising the below won't work when applying the MMM-YY format as the 31/05/2019 will be May-19 instead of Jun-19

I give up

 

[jaspalsd]

apologies I am not bumping as I can't edit the previous post - if admin can remove my last post that'll be great!

I changed the minimum date to May 1st 2019 but with a decimal, after trial and error. I also added intervals of 30.7, again with trial and error

If all else fails this will suffice but there is a possibility that problems may occur when other dates are added down the line