VBA – Convert Month Name to Number and Vice Versa

Posted on May 29, 2020 by

Convert number to name – [3 -> March]

Converting month number to name in VBA is actually coming out of the box with the MonthName() function, which is built-in the stanard VBA library:

Sub MonthNameExample()
    
    Dim i As Long
    For i = 1 To 12
        Debug.Print MonthName(i)
        Debug.Print Format(MonthName(i, True))
    Next
    
End Sub

Sub MonthNameExample()

Dim i As Long

For i = 1 To 12

Debug.Print MonthName(i)

Debug.Print Format(MonthName(i, True))

End Sub

The result is not flabbergasting, but expected:

Convert name to number – [March -> 3]

MonthName() is a built in function in VBA, but MonthNumber() does not exist. Unfortunately. That’s why we should write it:

Function MonthNumber(myMonthName As String)
       
    MonthNumber = Month(DateValue("1 " & myMonthName & " 2020"))
    MonthNumber = Format(MonthNumber, "00")
    
End Function

Function MonthNumber(myMonthName As String)

MonthNumber = Month(DateValue("1 " & myMonthName & " 2020"))

MonthNumber = Format(MonthNumber, "00")

End Function

And it works even a bit better than expected, accepting both “Feb” and “February” as an input:

Enjoy!

Tagged with: convert, month name, MonthName, MonthNumber, name to number, number