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
5 Comments:
And you could use an arbitrary day and month, instead of the Day and Year functions:
dtestr = monthName & "/1/2000"
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.
You would get -999 back, even if you pass in a valid month name. Consider the code amended.
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
Sheets("settings").Select
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],
convertMonthNameToNumber(union!R[0]C[0]))"
Else
Sheets("temp").Cells(2, j).FormulaR1C1 = "=union!R[0]C[0]"
End If
Sheets("temp").Select
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
Sheets("union").Select
Cells(2, j).Select
Range(Cells(2, j), Cells(lngRowMax, j)).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End If
Next j
Great solution !!!
André Luiz Bernardes
bernardess@gmail.com
http://inanyplace.blogspot.com/
Santos – SP – Brazil