Solved Convert number of month to name of month

May 2, 2009 at 16:09:27
Specs: windows
How do I convert the number of the month (1, 2, 3) to the month (Jan, Feb, Mar) in excel?

See More: Convert number of month to name of month

Report •


✔ Best Answer
May 6, 2009 at 05:17:27
My $0.02: For a number from 1 to 12, I'd use

=TEXT(DATE(2000,A1,1),"mmm")



#1
May 2, 2009 at 16:38:48
What do you mean "convert"?

Are you trying to retain the entire date but change the way it is displayed or are you trying to extract the month and put it in a cell by itself?


Report •

#2
May 3, 2009 at 06:08:00
While changing the way the date is displayed is my ultimate goal, extracting the month number, i.e. 5 = May...11=Nov from another column that I hide within the spreadsheet will also solve my problem. I am learning more about Vlookup and this excel function looks like a promising approach.

Report •

#3
May 3, 2009 at 07:41:28
To redesign the way your date’s display you have all the choices offered
in the Date section of cell formatting or you can “roll your own” using a Custom Format.

But for now try this:

With your Date in A1 try this in cell B1:

=TEXT(A1,”MMMMM”)
for the first letter of the month

=TEXT(A1,”MMMM”)
for the month spelled out completely

=TEXT(A1,”MMM”)
for the 3 letter abbreviation of month

If you have a cell with just a number from 1 thru 12 you are going to
have to use some type of =IF function, or VBA code to match up the
number with the correct Month.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 3, 2009 at 07:58:19
re: If you have a cell with just a number from 1 thru 12...

... you can use CHOOSE()

=CHOOSE(A1,"Jan","Feb","Mar","Apr","May", etc.)

You can also use CHOOSE with MONTH() if you have dates:

=CHOOSE(MONTH(D1),"Jan","Feb","Mar","Apr","May", etc.)


Report •

#5
May 3, 2009 at 10:06:07
Forgot about =CHOOSE
But it does make for a rather long formula, especially if you spell out the complete month name.
Not that I’m against long formulas, but I do prefer “short & sweet”.

MIKE

http://www.skeptic.com/


Report •

#6
May 3, 2009 at 12:44:23
Guys, Thanks a lot! The =Choose is the solution that I just tried and it works like a champ. One thing is for sure, there is more capability within excel than I will ever begin to tap into.

Report •

#7
May 3, 2009 at 15:24:39
If the CHOOSE option is too long for you, please show me how your IF suggestion would be “short & sweet”.


Report •

#8
May 3, 2009 at 15:43:54
The IF option was too long for my taste, that's why I opted for the TEXT solution, which you must admit, is a bit shorter than the CHOOSE solution.

MIKE

http://www.skeptic.com/


Report •

#9
May 3, 2009 at 15:59:18
Then why did you say "If you have a cell with just a number from 1 thru 12 you are going to have to use some type of =IF function..."

Report •

#10
May 3, 2009 at 17:22:05
In the first post, it was unclear if the contents of the cells in question had a date or simply a number.

If the Cells had only a number then the only option, that came to mind, was an =IF function, but that would have required two statements combined.
A daunting task for someone not accustomed to writing them.

I totally forgot about the =CHOOSE function.

When he replied with:

“While changing the way the date is displayed is my ultimate goal, extracting the month number, i.e. 5 = May...11=Nov from another column that I hide within the spreadsheet will also solve my problem.”

That then opened the field to include dates.

My first thought was to use the =MONTH function and a Custom Format of MMMM to get the month spelled out, but that does not work.

I then considered a =TEXT(MONTH(A1),”MMMM”) solution but opted for the shorter and simpler =TEXT(A1,”MMMM”)
that I suggested.

MIKE

http://www.skeptic.com/


Report •

#11
May 3, 2009 at 18:14:00
re: My first thought was to use the =MONTH function and a Custom Format of MMMM to get the month spelled out, but that does not work.

Works for me in 2003 Pro - all three formats mmm, mmmm & mmmmm

BTW...I was really offering CHOOSE just for the list of numbers, 1 - 12. The CHOOSE(MONTH(A1) was simply to show that many Excel functions can be combined.


Report •

#12
May 3, 2009 at 19:01:02
How did you get =MONTH to work?

In Column A, starting at A1 thru A13 I input he numbers 1 thru 13

In column B, I formatted cells B1 thru B13 as Custom Format, MMMM

I then entered the formula =MONTH(A1) in cell B1,
I then copied the formula down from B1 to B13 and all I got was January in every cell in column B.

The =MONTH function always required a Date to work.
Even then the custom format did not convert the number.

MIKE

http://www.skeptic.com/


Report •

#13
May 3, 2009 at 19:18:11
Sorry, last post it should read:

In Column A, starting at A1 thru A12 I input the Dates 1/1/07 thru 12/1/07.

In column B, I formatted cells B1 thru B12 as Custom Format, MMMM

MIKE

http://www.skeptic.com/


Report •

#14
May 3, 2009 at 21:20:32
You're right.

I must have tried a date in January, got January after the custom format, and assumed it worked for the other months.

I think what is happening is that as soon as you custom format the cell to be mmm Excel considers the cell to be a date_serial. Therefore, it sees 1 as 1/1/1900, 2 as 1/2/1900, etc. (Format those cells as dates and that's what you'll get.)

Since MONTH will always return 1 - 12, we'll always get January if we format with the m's.


Report •

#15
May 4, 2009 at 06:27:01
Makes sense.

MIKE

http://www.skeptic.com/


Report •

#16
May 6, 2009 at 05:17:27
✔ Best Answer
My $0.02: For a number from 1 to 12, I'd use

=TEXT(DATE(2000,A1,1),"mmm")


Report •

#17
May 6, 2009 at 19:31:27
Well okay, the =TEXT(DATE(2000,A1,1),"mmm") works perfectly and is much shorter than the =choose approach.

I greatly appreciate all the discussions here.


Report •


Ask Question