Displaying 1-12 numbers as months of the year

[Mary90]

Hi guys

I have a graph that uses the actual numbers 1-12 to display months, but I'd like the data label to display the month written out as text.

Since the number must stay as an integer between 1 and 12, I cannot use date type formulae, since they then need the number to become a date number. Is there a way to create a number format to do this? "mmm" will obviously not work. (i.e., 12 should be "December" and not "12 Jan 1900" as it will currently be interpreted as)

Thanks!

 

[Shoemaker]

=IF(A1=1,"January",IF(A1=2,"February",IF(A1=3,"March",IF(A1=4,"APRIL",IF(A1=5,"MAY",IF(A1=6,"JUNE",IF(A1=7,"JULY",IF(A1=8,"AUGUST",IF(A1=9,"SEPTEMBER",IF(A1=10,"OCTOBER",IF(A1=11,"NOVEMBER",IF(A1=12,"DECEMBER",0))))))))))))

This statement will return the month name for the number in cell A1. Insert next to the column of month numbers in your chart data and source the formula column as the data labels. There is probably a better way but this should work fine.

 

[JimM]

No it's not what you want but a simplified version of the above would be

=CHOOSE(A1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

 

[Mary90]

No it's not what you want but a simplified version of the above would be

=CHOOSE(A1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Thanks Jim. This is what I'm currently using. I was just wondering if it's possible to do it as a number format.

Cheers!

 

[Andrew Poulsom]

I'm not sure about a number format, but you could try:

=TEXT("1-"&A1,"mmm")

 

[Rick Rothstein]

I'm not sure about a number format, but you could try:

=TEXT("1-"&A1,"mmm")

That did not work for me (US settings), it returned January for each number between 1 and 12. This formula did work for me though...

=TEXT(28*A1,"mmm")

 

[Andrew Poulsom]

US style dates are the bane of most people's lives

 

[anthonyexcel]

Rick just trying to understand why 28*A1?

 

[Rick Rothstein]

Rick just trying to understand why 28*A1?

Actually, you can use 28, 29 or 30 and the formula will work. Dates, to Excel, are simply the number of days offset from some fixed starting date... Excel uses 12/31/1899 as that starting date. Today's date (Jan 20, 2016) is 42389 days offset from that starting date (you can see this by putting today's date in a cell and then changing that cell's format to General). This offset number is all Excel knows about a date... everything you (the human) wants to know about it (month, day, format, and so on) is calculated by Excel from that offset number.

Now, back to your question... 28*1=28, 28*2=56, 28*3=84, etc. each of these calculated numbers represents an offset within 1900 (the first year Excel "knows about"). As it turns out, each one of those offsets takes you to the date within a different month in 1900 (28=>Jan 28, 56=>Feb 25, 84=>Mar 24, etc.), so having the TEXT function tell you the month name for the offset date ends up telling you the appropriate month name for the 1st, 2nd, 3rd, etc. month in a year. If you use numbers other than 28, 29 or 30, the drift within the months end up not confining themselves to the correct sequential month.

 

[Jonmo1]

I'm not sure about a number format, but you could try:

=TEXT("1-"&A1,"mmm")

That did not work for me (US settings), it returned January for each number between 1 and 12. This formula did work for me though...

=TEXT(28*A1,"mmm")

US style dates are the bane of most people's lives

That's why I like the DATE function, it avoids all ambiguity
TEXT(DATE(1,A1,1),"mmm")

But the A1*28 trick is pretty cool