[Solved] Turning Month Names into Numeric equivalent

[qaz1qaz1qa]

I have a drop down list of the Months by name. Is there a way to turn these into the numeric equivalent?

I suppose I could use a Vlookup or some kind of chart comparison. But for some reason in the back of my head I think this exists already as a function. The ones I come across all require a numeric value.

[squenson]

You could use a formula like:
=MONTH("01-"&D10&"-01")
Replace D10 by the cell containing the month

[Charlie Young]

Code: Select all

=MATCH(MonthName;{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"};0)

or

Code: Select all

=MATCH(MonName;{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0)

[qaz1qaz1qa]

Not sure why I never thought of Put an array inside the formula. Perfect solution thanks Charlie

@squenson
Not sure how that formula works. Could you explain it as it does not work on my sheet.
from what i gather you are concatenating -1 with contents of cell D6 but how does that work?

[RoryOF]

Look at squenson's answer again. He generates a date 01/Month/01 and then extracts the month number. He uses - as delimiter, whereas I used / as delimiter; which is correct will depend on system locale settings, I think.

[Villeroy]

squenson's formula can not work. It used to work in older versions but that was considered as a bug.
MONTH("2001-12-01") still works because it is an unambiguous ISO date with 8 digits and 2 dashes,
which is the same as MONTH("37226") converting an unambiguous integer (digits only).
Any other type of numeric string is ambiguous or language dependent.

VALUE converts from numeric string to number in the context of the current application locale. VALUE may fail or it may give wrong values when you pass the document to someone else or when you switch your own locale setting.
MONTH(VALUE("01-Dec-01")) may work if the application locale evaluates "Dec" correctly.
On my system it had to be
MONTH(VALUE("01-Dez-01")) since I use a German locale most of the time.
MONTH(VALUE("1. déc. 2001")) under a French locale.

[squenson]

squenson's formula can not work. It used to work in older versions but that was considered as a bug.

I tested the formula with "Jan", "January", "1" and all worked! I am using LibreOffice 3.4.4. as mentioned in my signature. Of course, any formula has to be adapted to the local settings of the OP.

[Villeroy]

Well, the LibreOffice folks may have reintroduced that bug for the sake of Excel compatibility. I hold my breath for the mighty LibO hackers to implement the day without sunrise (1900-02-29).
I confirm that LibO 3.5 evaluates MONTH("01-Dez-01") under a German locale.

[qaz1qaz1qa]

The only manipulation of the code I could get to work was
=MONTH(VALUE(CONCATENATE("01-";D10;"-01")))
I was forced to introduce Concatenate() and remove the & inside the Value() function.
No other combination would produce anything but #VALUE

Think I like about Charlies answer is the system does not need to recognize the significance of my text, only it's position in the array. Nor do I need to use up cell space.

BUT since I do have a satisfactory answer with a few possible methods I will mark this BAM! [SOLVED]

[Villeroy]

=MONTH(VALUE("01-"&D10&"-01")) is exactly the same.

[qaz1qaz1qa]

Yes I am aware of that.
But only after switching to the Concatenate version would the code work non error.
However now that I re-input the code it accepts it. Crazy .