Excel convert Month Name ("MMM") to Month number (1-12)

Ran into a problem with Excel and dates, I have the month name (as Jan-Dec), and I need to convert it to the month number using a formula, is there any way to do this?

hi,

Build a table of month names.

Use the Match function to return the month number.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

.. and if you want it all in a single formula, you can build the array on the fly ..

=MATCH(YourCellRef,TEXT(DATE(2000,ROW(1:12),1),"Mmm"),0)

 .. array-entered (with Ctrl+Shift+Enter)

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

Another method:

=MONTH(DATEVALUE("1/"&YourCellRef&"/2000"))

that doesn't need to be an array formula.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

If your month name is in cell A2, then =Month(1&A2)

Vita Brevis

Verrrrry nice, xlhelp. Short, simple and functional.

->*

-John
    The plural of anecdote is not data

Help us help you. Please read FAQ 181-2886 before posting.

Wowie wow wow!

Where do you get these things, xlhelp?

Why does it work?

--Lilliabeth

Whoa, xlhelp!  ==> *

Can you 'splain that?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

If I may....

It returns 1Feb. Go type 1Feb into Excel. Excel will automatically change it to 1-Feb, a date.

Now, concatenating 1 & "Feb" will only return the string "1Feb" - not a date.

But - wrapping the concatenation 1 & "Feb" inside the MONTH function coerces the string back into a date, of which it then returns the month number.

-John
    The plural of anecdote is not data

Help us help you. Please read FAQ 181-2886 before posting.

Thanks, John.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

Nice, xlhelp, have a star.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

And it works great with a full name of the month, too - just tested.

You guys are too kind. Thank you though; celestial objects(*) definitely lift your spirits.

To 'splain it: I don't believe I could have articulated any better than John. Basically it's the same as saying =Month((1&Cellref)*1). The downside of it is that it gives you the first of the month for the current year. I use it in a spreadsheet where I don't care what date it translates to.

Vita Brevis

I like to know all these nuances.

One of my favorite useful ones is entering Jan 2009, then drag the "copy box" down.

Result, the FIRST of every month following.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}