Converting month name to number

In Convert MonthName to Month Number, Kent Lau posts some VBA code for converting month names to numbers. I replied with some code that is shorter and uses built in functions instead of hardcoding the month names in the code.

You can view his blog for his original code (update 9/21/2009: the site appears to be gone). Here is a slightly revised version of the code I posted as a comment. This code lets you do the same, without having to use three letter month abbreviations. You can write "Jan" or "January" and the code will work. It returns -999 if it was unsuccessful.

The blind error handling is necessary because the code assumes that no spellchecking was done beforehand.

Function convertMonthName2Number(monthName As String) As Integer

' try to convert month name to actual date type
Dim dtestr As String
dtestr = monthName & "/1/2000"

Dim dte As Date
On Error Resume Next
dte = CDate(dtestr)

If Err.Number <> 0 Then
  convertMonthName2Number = -999
  Exit Function
End If
On Error Goto 0

convertMonthName2Number = Month(dte)
End Function
About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Related Articles:

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 5 Comment(s) on Converting month name to number:

  1. And you could use an arbitrary day and month, instead of the Day and Year functions:

    dtestr = monthName & "/1/2000"

  2. Michael Pierce writes:

    I agree with Debra – if you ran this function on the 31st of the month, but passed in a month name that doesn't have 31 days, won't you get an error back? Debra's approach prevents that from happening since every month has at least 1 day. :)

  3. You would get -999 back, even if you pass in a valid month name. Consider the code amended.

  4. Hi,

    can you help me with this problem? I did it to convert Month name to Number and it turns out #value…
    from my original data the date looks like this: Jan 58 and i want it just to take the month out and turn it into a number.
    Is this possible?

    For j = 1 To lngUColMax
    If Application.WorksheetFunction.CountIf(Sheets("Settings").Range(Cells(12, 4), Cells(62, 4)), Sheets("union").Cells(1, j)) 0 Then

    If (j >= 10) And (j <= 15) Then
    Sheets("temp").Cells(2, j).FormulaR1C1 = "=IF(ISTEXT(Union!R[0]C[0]),union!R[0]C[0],
    Sheets("temp").Cells(2, j).FormulaR1C1 = "=union!R[0]C[0]"
    End If

    Cells(2, j).Select
    Sheets("temp").Cells(2, j).AutoFill Destination:=Range(Cells(2, j), Cells(lngURowMax, j)), Type:=xlFillDefault
    Range(Cells(2, j), Cells(lngURowMax, j)).Copy
    Range(Cells(2, j), Cells(lngURowMax, j)).PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    Range(Cells(2, j), Cells(lngURowMax, j)).Copy
    Cells(2, j).Select
    Range(Cells(2, j), Cells(lngRowMax, j)).PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    End If
    Next j

  5. Great solution !!!

    André Luiz Bernardes
    Santos – SP – Brazil

This article is closed to any future comments.
Random Data Generator