Code Snippets → Get Month Number From Month Name

Here are two ways of getting that information. The second is more compact than the first.

Function RetMonthNum(strMonthName As String) As Integer

Select Case strMonthName
   Case "January", "Jan"
      RetMonthNum = 1

   Case "Februrary", "Feb"
      RetMonthNum = 2

   Case "March", "Mar"
      RetMonthNum = 3

   Case "April", "Apr"
      RetMonthNum = 4

   Case "May"
      RetMonthNum = 5

   Case "June", "Jun"
      RetMonthNum = 6

   Case "July", "Jul"
      RetMonthNum = 7

   Case "August", "Aug"
      RetMonthNum = 8

   Case "September", "Sep"
      RetMonthNum = 9

   Case "October", "Oct"
      RetMonthNum = 10

   Case November", "Nov"
      RetMonthNum = 11

   Case December", "Dec"
      RetMonthNum = 12

End Select
End Function

Or here’s another method, (courtesy of Bob Askew):

'*******************************************
Public Function RtnMonthNum(pstrMonName As String) As Integer
'*******************************************
'Purpose:   Returns month number (1 - 12)
'           when provided a complete or partial
'           month name.
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? RtnMonthNum("April")
'           2) ? RtnMonthNum("Sep")
'Output: 1) 4
'           2) 9
'*******************************************
 
Dim strHold  As String
Dim strMonth As String
Dim intMonth As Integer
 
   strMonth = "JanFebMarAprMayJunJulAugSepOctNovDec"
   strHold = Left(pstrMonName, 3)
   intMonth = InStr(strMonth, strHold)
   RtnMonthNum = intMonth \ 3 + 1
 
End Function