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

jaspalsd

Board Regular
Joined
Feb 3, 2014
Messages
51
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Board Regular
Joined
Feb 3, 2014
Messages
51
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

 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Board Regular
Joined
Feb 3, 2014
Messages
51
. .

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

Board Regular
Joined
Feb 3, 2014
Messages
51
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 :)

 
Last edited:

jaspalsd

Board Regular
Joined
Feb 3, 2014
Messages
51
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

 

Forum statistics

Threads
1,081,706
Messages
5,360,769
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top