convert text month to numeric month

  • Thread starter Thread starter salgud
  • Start date Start date
S

salgud

I just asked here how to do the opposite. How do I convert "May" to 5
without using a case statement? I've looked all over on the net, but can't
find this specific thing.

Thanks in advance.
 
I just asked here how to do the opposite. How do I convert "May" to 5
without using a case statement? I've looked all over on the net, but can't
find this specific thing.

Thanks in advance.
Click to expand...

Try this worksheet formula:

=SUMPRODUCT(ROW(1:12)*(TEXT(DATE(,ROW(1:12),1),"MMM")="May"))

Hope this helps / Lars-Åke
 
Try this worksheet formula:

=SUMPRODUCT(ROW(1:12)*(TEXT(DATE(,ROW(1:12),1),"MMM")="May"))

Hope this helps / Lars-Åke
Click to expand...

Sorry, should have been clear I want to do this in VBA, not in the
spreadsheet.
 
Here is one way:
=CHOOSE(MATCH(A1,{"Jan";"Feb";"Mar";"Apr";"May"},1),"1","2","3","4","5")

Another way:
=LOOKUP(A17,{"Jan","Feb","Mar","Apr","May"},{"1","2","3","4","5"})


HTH,
Ryan---
 
strMonth = "May"
intMonth = Month("01-" & strMonth & "-" & Year(Date))

If this post helps click Yes
 
Hmmmmm

1) Enter the values below in A1 to A12
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

2) In a general module, copy the following function:

Function test(tMth As Range)

test = Month(CVDate(tMth.Value & "-1-2000"))

End Function

3) Enter "=test" (without quotes) in B1.
4) copy down to B12

I get the numbers 1 to 12 in XL 2003.
Click to expand...

Thanks again. I ended up using Don's method. (see below)
 
Hmmmmm

1) Enter the values below in A1 to A12
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

2) In a general module, copy the following function:

Function test(tMth As Range)

test = Month(CVDate(tMth.Value & "-1-2000"))

End Function

3) Enter "=test" (without quotes) in B1.
4) copy down to B12

I get the numbers 1 to 12 in XL 2003.
 
Back
Top