CODEWITHSUNDEEP
exceldatevba
HL8
HL8

Reputation: 1419

Convert month name into number

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

Upvotes: 13

Views: 90711

Answers (6)

T.M.
T.M.

Reputation: 9948

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

Upvotes: -1

Alexus
Alexus

Reputation: 1973

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))

Upvotes: 0

ThiamTeck
ThiamTeck

Reputation: 385

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

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

Upvotes: 0

Inaki Melchor
Inaki Melchor

Reputation: 9

Sub month()

Dim monthh As Integer

monthh = month(Date)

MsgBox monthh

End Sub

try this.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

Upvotes: 45

Bharat Sinha
Bharat Sinha

Reputation: 14363

Try this...

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

Where A1 cell contains month name.

Upvotes: 5

Related Questions