month text to a number. Why does this work?

[sark666]

I knew there was a way to convert a month written in text (e.g. May) to a number (5), but couldn't recall how.

I thought it was simply =Month("May") but that results in a #value error. However if you simply append any number from 0-99 it works. Actually, I don't see the logic with the appended numbers either. "May100" results in an error but "May9000" gives 5. Huh?

E.g. =Month("May0") or =Month("May84") will both return 5.

Sometimes I just accept things but usually like it to make sense. Anyone see the logic as to why this works?

 

[Andrew Poulsom]

Enter May84 in a cell and it will be coerced to the date 01 May 1984. That's why the MONTH function works. May is treated as text.

 

[sark666]

I should have realized it was interupting the year. Thanks.

 

[sark666]

Another question on this: How do you do the same in vba?

doing month("April1") in vba results in a type mismatch error and you can use application.worksheetfunction.month as month is available in vba.

Yes, I could do it via a lookup table or something but wondered if a quick solution similar to the above method would work in vba.

 

[Andrew Poulsom]

This worked for me:

MsgBox Month(DateValue("May 1"))

 

[sark666]

That it does. Thanks again, this is handy. Hmm, I wouldn't have thought to use datevalue....

Oh and there was a typo in my vba question as you CAN'T use app.workshtfnt.month...