[Solved] Turning Month Names into Numeric equivalent

Discuss the spreadsheet application

[Solved] Turning Month Names into Numeric equivalent

Postby qaz1qaz1qa » Sat Feb 04, 2012 10:10 am

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.
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.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: Turning Month Names into Numeric equivelant

Postby squenson » Sat Feb 04, 2012 10:33 am

You could use a formula like:
=MONTH("01-"&D10&"-01")
Replace D10 by the cell containing the month
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Turning Month Names into Numeric equivelant

Postby Charlie Young » Sat Feb 04, 2012 10:39 am

Code: Select all   Expand viewCollapse view
=MATCH(MonthName;{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"};0)


or

Code: Select all   Expand viewCollapse view
=MATCH(MonName;{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0)
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Turning Month Names into Numeric equivalent

Postby qaz1qaz1qa » Sat Feb 04, 2012 7:42 pm

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?
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.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: Turning Month Names into Numeric equivalent

Postby RoryOF » Sat Feb 04, 2012 7:49 pm

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.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 30624
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Turning Month Names into Numeric equivalent

Postby Villeroy » Sat Feb 04, 2012 8:32 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27879
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Turning Month Names into Numeric equivalent

Postby squenson » Sat Feb 04, 2012 10:30 pm

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.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Turning Month Names into Numeric equivalent

Postby Villeroy » Sat Feb 04, 2012 10:34 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27879
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Turning Month Names into Numeric equivalent

Postby qaz1qaz1qa » Sat Feb 04, 2012 10:40 pm

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]
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.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: Turning Month Names into Numeric equivalent

Postby Villeroy » Sat Feb 04, 2012 10:43 pm

=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 188 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27879
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Turning Month Names into Numeric equivalent

Postby qaz1qaz1qa » Sun Feb 05, 2012 3:10 am

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 .
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.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot], MSN [Bot] and 37 guests