[Solved] Turning Month Names into Numeric equivalent
- qaz1qaz1qa
- Posts: 103
- Joined: Fri Jan 27, 2012 1:36 am
[Solved] Turning Month Names into Numeric equivalent
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.
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.
Last edited by qaz1qaz1qa on Sat Feb 04, 2012 10:43 pm, edited 1 time in total.
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
Re: Turning Month Names into Numeric equivelant
You could use a formula like:
=MONTH("01-"&D10&"-01")
Replace D10 by the cell containing the month
=MONTH("01-"&D10&"-01")
Replace D10 by the cell containing the month
LibreOffice 4.2.3.3. on Ubuntu 14.04
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Turning Month Names into Numeric equivelant
Code: Select all
=MATCH(MonthName;{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"};0)
Code: Select all
=MATCH(MonName;{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0)
Apache OpenOffice 4.1.1
Windows XP
Windows XP
- qaz1qaz1qa
- Posts: 103
- Joined: Fri Jan 27, 2012 1:36 am
Re: Turning Month Names into Numeric equivalent
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?
@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?
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
Re: Turning Month Names into Numeric equivalent
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Turning Month Names into Numeric equivalent
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Turning Month Names into Numeric equivalent
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.squenson's formula can not work. It used to work in older versions but that was considered as a bug.
LibreOffice 4.2.3.3. on Ubuntu 14.04
Re: Turning Month Names into Numeric equivalent
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.
I confirm that LibO 3.5 evaluates MONTH("01-Dez-01") under a German locale.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- qaz1qaz1qa
- Posts: 103
- Joined: Fri Jan 27, 2012 1:36 am
Re: Turning Month Names into Numeric equivalent
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]
=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]
Last edited by qaz1qaz1qa on Sat Feb 04, 2012 10:45 pm, edited 1 time in total.
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
Re: Turning Month Names into Numeric equivalent
=MONTH(VALUE("01-"&D10&"-01")) is exactly the same.
- Attachments
-
- date_strings.ods
- explicit and implicit conversion of ambiguous date strings
- (8.78 KiB) Downloaded 303 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- qaz1qaz1qa
- Posts: 103
- Joined: Fri Jan 27, 2012 1:36 am
Re: [Solved] Turning Month Names into Numeric equivalent
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 .
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 .
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.