Convert month name to number

Question

I have a drop down list with months. When a month is selected, I would then like to convert it to a month number, is there a function that can do this?

Eg. September = 9

Answer 1

Another way

Excel Formula

=MONTH(1&A1) 

Vba

 Sub Sample() Dim MonthNm As String MonthNm = "September" Debug.Print Month(DateValue("01 " & MonthNm & " 2012")) End Sub 

or

 Sub Sample() Dim MonthNm As String MonthNm = "September" Debug.Print Application.Evaluate("=MONTH(1&" & Chr(34) & MonthNm & Chr(34) & ")") End Sub 

Replace

Answer 2

Try it...

  =MONTH(DATEVALUE(A1&"1")) 

Where A1 cell contains the name of the month.

Answer 3

 Sub month() Dim monthh As Integer monthh = month(Date) MsgBox monthh End Sub 

try it.

Answer 4

another excel formula where A1 is the cell id with the month name:

 =TEXT(DATEVALUE(A1&" 1"), "m") 

Answer 5

This solution did not work for me (Excel 2010), I had to shorten the month name to 3 characters and add the day number in front of the shortened line.

 =MONTH(1&LEFT(A1;3)) 

Answer 6

Another VBA solution

For art's sake, besides Siddhart, the valid answer is :-)

 Sub SampleTM() Dim MonthNm$: MonthNm = "September" Debug.Print MonthNm2Num(MonthNm) End Sub Function MonthNm2Num(ByVal MonthNm) As Long MonthNm2Num = Format(CDate(MonthNm & "/1 0"), "m") * 1& End Function