Convert month name into number

Question

I have dropdown box with months populated. When a month is selected I would like to then convert it to the month number is there a function that can do this?

Eg. September = 9

score 44 · Accepted Answer · answered Aug 10 '12 at 04:07

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

score 5 · Answer 2 · answered Aug 10 '12 at 03:59

5

Try this...

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

Where A1 cell contains month name.

answered Aug 10 '12 at 03:59

Bharat Sinha

13,973
6
39
63

Yesterday I was looking at your profile and when I saw this question I was quite sure you will be the first one to answer... but when it was not responded; I thought let me give it a try. thanks!! – Bharat Sinha Aug 10 '12 at 05:23
I already knew the answer but wanted to test all three before I posted (An uncanny habit I must say) :) – Siddharth Rout Aug 10 '12 at 05:25

score 0 · Answer 3 · edited Feb 12 '14 at 00:39

0

Sub month()

Dim monthh As Integer

monthh = month(Date)

MsgBox monthh

End Sub

try this.

edited Feb 12 '14 at 00:39

kleopatra

51,061
28
99
211

answered Feb 12 '14 at 00:17

Inaki Melchor

9
1

score 0 · Answer 4 · answered Feb 29 '16 at 16:20

0

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

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

answered Feb 29 '16 at 16:20

ThiamTeck

385
1
9

score 0 · Answer 5 · answered Jan 05 '17 at 09:55

0

This solution didn't 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 string.

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

answered Jan 05 '17 at 09:55

Alexus

1,887
1
23
50

score -1 · Answer 6 · answered Feb 06 '19 at 20:45

Another VBA solution

For the sake of the art in addition to Siddharth's valid answer :-)

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

Convert month name into number

6 Answers6

Linked

Related