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

[skipjakk]

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?

 

[SkipVought]

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

 

[TonyJollans]

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

[blue][tt]=MATCH([/tt][red][tt]YourCellRef[/tt][/red][tt],TEXT(DATE(2000,ROW(1:12),1),"Mmm"),0)[/tt][/blue]

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

 

[GlennUK]

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.

 

[xlhelp]

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

Vita Brevis

 

[AnotherHiggins]

Verrrrry nice, xlhelp. Short, simple and functional.

->*

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

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

 

[Lilliabeth]

Wowie wow wow!

Where do you get these things, xlhelp?

Why does it work?

--Lilliabeth

 

[SkipVought]

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

 

[AnotherHiggins]

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.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

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

 

[SkipVought]

Thanks, John.

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

 

[TonyJollans]

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

 

[Stella740pl]

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

 

[xlhelp]

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

 

[SkipVought]

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