Month: Word to Number

[Sarah K]

Is there a formula in Excel VB that will allow me to convert a month's name to its numberic equivalent?

So for example:
October to 10
June to 6 etc...


I know I can do a lookup or something, but it there anything slicker?
Any help would be great

 

[Jonmo1]

Try

=MONTH(A1&"1")

 

[kpark91]

Try

=MONTH(A1&"1")

Whooaaa.
How come that workedd??

 

[Jonmo1]

Whooaaa.
How come that workedd??

If A1 = October
then
A1&"1" = October1
but that's just a text string right??

The month function extracts the month # from a given date.
And it does the best it can to convert text strings into Dates.
In this case, October1 is converted to October 1st 2011(current year)

it will even work with abbreviated months...
Oct, Nov, Dec, Jan etc..

 

[kpark91]

If A1 = October
then
A1&"1" = October1
but that's just a text string right??

The month function extracts the month # from a given date.
And it does the best it can to convert text strings into Dates.
In this case, October1 is converted to October 1st 2011(current year)

That's such a neat trick :D
Thank you for the explanation!

Kpark.

 

[Sarah K]

Thank's for your quick reply.

I am looking for something that works as code in Microsoft Visual basic, not in the cell of the excel worksheet, any thoughts?

 

[T. Valko]

Whooaaa.
How come that workedd??

It'll even work without the quotes:

=MONTH(A1&1)

It'll even work if you have a jacked up month name:

Octo

 

[prajul89]

That's really cool

 

[prajul89]

Once I had to write a whole user defined function for this.
Stupid me.

 

[Jonmo1]

You can do it almost exactly the same in VBA...
You just have to put a space between October and 1

Try
x = Month(Range("A1") & " 1")