Converting A Month Text String To A Corresponding Month Number
A month text string ("Jan", "Feb", ... "Dec") can be converted to a corresponding number from 1 to 12 using arrays, but this example shows how to take advantage of built-in Excel functions to accomplish the same objective.
Program Code
Option Explicit
Option Base 1
Public Sub TestDateConversion()
' ******************************************************************
' This Will Convert the Strings "Jan", "Feb", etc. to A Month Number
' ******************************************************************
Dim intMonthNumber As Integer
intMonthNumber = ConvertMonthNameToNumber("Mar")
End Sub
Public Function ConvertMonthNameToNumber(MonthName As String) As Integer
' ******************************************************************
' The Passed Argument to This Routine is a single month abbreviation
' i.e. "Mar"
' ******************************************************************
Dim strFullDateStringWithAlphaMonth As String
Dim dteConvertedDate As Date
strFullDateStringWithAlphaMonth = MonthName & "/1/2000"
' ******************************************************************
' If The Date String Does Not Conform, Return -999 Error Code
' ******************************************************************
On Error Resume Next
dteConvertedDate = CDate(strFullDateStringWithAlphaMonth)
If Err.Number <> 0 Then
ConvertMonthNameToNumber = -999
Exit Function
End If
On Error GoTo 0
ConvertMonthNameToNumber = Month(dteConvertedDate)
End Function
