convert text month to numeric month
 

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.

convert text month to numeric month
 

On Tue, 24 Mar 2009 15:44:22 -0600, salgud
wrote:

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.

Try this worksheet formula:

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

Hope this helps / Lars-Åke

convert text month to numeric month
 

On Tue, 24 Mar 2009 22:03:30 GMT, Lars-Åke Aspelin wrote:

On Tue, 24 Mar 2009 15:44:22 -0600, salgud
wrote:

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.

Try this worksheet formula:

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

Hope this helps / Lars-Åke

Sorry, should have been clear I want to do this in VBA, not in the
spreadsheet.

convert text month to numeric month
 

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"salgud" wrote:

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.

convert text month to numeric month
 

One way
mymonthnum = Month(DateValue(Range("i2") & " 1,2009"))



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"salgud" wrote in message
.. .
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.

convert text month to numeric month
 

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

If this post helps click Yes
---------------
Jacob Skaria

"salgud" wrote:

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.

convert text month to numeric month
 

Debug.Print Month(CVDate(tMth & "-1-2000"))

--
Steve

"salgud" wrote in message
...
On Tue, 24 Mar 2009 22:03:30 GMT, Lars-Åke Aspelin wrote:

On Tue, 24 Mar 2009 15:44:22 -0600, salgud
wrote:

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.

Try this worksheet formula:

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

Hope this helps / Lars-Åke

Sorry, should have been clear I want to do this in VBA, not in the
spreadsheet.

convert text month to numeric month
 

Forgot to add tMth is your text month.

--
Steve

"AltaEgo" <Somewhere@NotHere wrote in message
...
Debug.Print Month(CVDate(tMth & "-1-2000"))

--
Steve

"salgud" wrote in message
...
On Tue, 24 Mar 2009 22:03:30 GMT, Lars-Åke Aspelin wrote:

On Tue, 24 Mar 2009 15:44:22 -0600, salgud
wrote:

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.

Try this worksheet formula:

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

Hope this helps / Lars-Åke

Sorry, should have been clear I want to do this in VBA, not in the
spreadsheet.

convert text month to numeric month
 

On Fri, 27 Mar 2009 09:42:11 +1100, AltaEgo wrote:

Forgot to add tMth is your text month.

Thanks for trying, but that formula doesn't work, it always returns Jan.
Any other suggestions?

convert text month to numeric month
 

On Thu, 2 Apr 2009 10:35:43 +1100, AltaEgo wrote:

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.

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

convert text month to numeric month
 

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.

--
Steve

"salgud" wrote in message
.. .
On Fri, 27 Mar 2009 09:42:11 +1100, AltaEgo wrote:

Forgot to add tMth is your text month.

Thanks for trying, but that formula doesn't work, it always returns Jan.
Any other suggestions?