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.

On Tue, 24 Mar 2009 15:44:22 -0600, salgud <(E-Mail Removed)>
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),"MMM")="May"))

Hope this helps / Lars-Åke

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

> On Tue, 24 Mar 2009 15:44:22 -0600, salgud <(E-Mail Removed)>
> 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),"MMM")="May"))
>
> Hope this helps / Lars-Åke

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

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

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



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"salgud" <(E-Mail Removed)> wrote in message
news:zsffhhqi5yyd$.1f36a33efjg2i$.(E-Mail Removed)...
>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.

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

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

--
Steve

"salgud" <(E-Mail Removed)> wrote in message
news:a5yoa2x0z78l$.(E-Mail Removed)...
> On Tue, 24 Mar 2009 22:03:30 GMT, Lars-Åke Aspelin wrote:
>
>> On Tue, 24 Mar 2009 15:44:22 -0600, salgud <(E-Mail Removed)>
>> 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),"MMM")="May"))
>>
>> Hope this helps / Lars-Åke
>
> Sorry, should have been clear I want to do this in VBA, not in the
> spreadsheet.

Forgot to add tMth is your text month.

--
Steve

"AltaEgo" <Somewhere@NotHere> wrote in message
news:#(E-Mail Removed)...
> Debug.Print Month(CVDate(tMth & "-1-2000"))
>
> --
> Steve
>
> "salgud" <(E-Mail Removed)> wrote in message
> news:a5yoa2x0z78l$.(E-Mail Removed)...
>> On Tue, 24 Mar 2009 22:03:30 GMT, Lars-Åke Aspelin wrote:
>>
>>> On Tue, 24 Mar 2009 15:44:22 -0600, salgud <(E-Mail Removed)>
>>> 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),"MMM")="May"))
>>>
>>> Hope this helps / Lars-Åke
>>
>> Sorry, should have been clear I want to do this in VBA, not in the
>> spreadsheet.
>

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?

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)