[Solved] Convert 3-letter Month to Numeric Format

Discuss the spreadsheet application

[Solved] Convert 3-letter Month to Numeric Format

Postby crusader » Wed Feb 13, 2019 3:04 am

After many [unsuccessful] attempts on my own, and searching this forum, I am requesting help with the following.

I have a list of months of the year in 3 alphabet characters (e.g. Feb, Sep, etc.). I am looking for a way to change that to the month's corresponding number (e.g. 2, 9, etc.). I have tried multiple versions of DATEVALUE and MONTH, to no avail. I am using LO 6.2.

Thank you for the support!
Last edited by crusader on Wed Feb 13, 2019 3:12 pm, edited 1 time in total.
LO 6.x on Linux Mint 18.2 Cinnamon
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 489
Joined: Sun Jan 20, 2008 5:06 am

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby robleyd » Wed Feb 13, 2019 3:10 am

Would a lookup table and VLOOKUP work for you?
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2862
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby crusader » Wed Feb 13, 2019 3:32 am

Thank you for the quick response, Robleyd.

robleyd wrote:Would a lookup table and VLOOKUP work for you?

If that is the only choice, yes. :( I am trying to avoid additional measures (even if they are automated - a 12 row VLOOKUP would be a breeze). My research showed Excel does it with
=MONTH(DATEVALUE(A2&1)) where the 3-letter month abbreviation is in cell A2.
However, this, nor numerous related attempts worked with LO. I am sharing the Excel formula in the hope someone can find a fix in LO.
LO 6.x on Linux Mint 18.2 Cinnamon
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 489
Joined: Sun Jan 20, 2008 5:06 am

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby Villeroy » Wed Feb 13, 2019 3:44 am

You have to build a formula from inside out. =A2&1 gives what? Jan1. Jan1 is not a date by any means.
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby crusader » Wed Feb 13, 2019 4:22 am

Thank you, Villeroy.

I used Excel 2007 to test multiple instances of the formula I shared above - and it works - as presented. If it helps, according to one website, "(&"1" is added for the DATEVALUE function to understand it's a date)."

Please note: the numeral 1 (one) in the formula can be inside quotation marks or without quotation marks; it can also precede the cell reference as shown below.

=MONTH(DATEVALUE(1&A2))
LO 6.x on Linux Mint 18.2 Cinnamon
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 489
Joined: Sun Jan 20, 2008 5:06 am

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby Zizi64 » Wed Feb 13, 2019 8:10 am

I think you need format only the numeric result by the format code "MMM".

Note:
The effect of the format code of the "MMM" is different at different locale settings!

Hungarian: MMM = Roman numerals
II.

English, USA : MMM = Three letter abbreviation with capital first letter
Feb

Spanish, Esperanto: MMM = Three letter abbreviation without capital first letter
feb

Serbian Cyrillic:
феб


Check it in the menu:
Tools - Options - Language settings - Locale setting
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8133
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby Lupp » Wed Feb 13, 2019 12:24 pm

This may be one of the very few cases where an inline array can be helpful if the task is clearly, permanently, and unambiguously localized:
Code: Select all   Expand viewCollapse view
=MATCH(A2;{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"};0)

(MATCH applied to texts is always case-insensitive.)

A generalizing approach you find in the attached example.
Attachments
aoo96980GeneralizedLookupOfMMMabbreviations_1.ods
(14.87 KiB) Downloaded 18 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2522
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby Villeroy » Wed Feb 13, 2019 12:57 pm

Indeed, =VALUE("Jan1") gives the day number of this year's 1st of January in US locale context which is 43466.
=DATEVALUE("Jan1") is just the same. It would cut off any time portion as in INT(VALUE("Jan 1 2019 9:00pm")).

In UK context =VALUE("Jan1") gives the day number of "January 2001", more precisely 1st of January 2001 which is day number 36892, according to the usual default settings for 2-digit dates where 1 or 01 translates to 2001.

LibreOffice does not interprete Jan1 as anything numeric which is why I was wrong.
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby mikele » Wed Feb 13, 2019 3:00 pm

Hello,
in DE local context (tested under LO5.4.6) =DATEVALUE("Jan1") results an error but =DATEVALUE("Jan-1") works. It results 1st of January 2001.
So maybe
Code: Select all   Expand viewCollapse view
=MONTH(DATEVALUE(A2&"-1"))
solves the problem?
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 44
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby crusader » Wed Feb 13, 2019 3:02 pm

Thank you for the support and suggestions.

It does not appear there is a simple formula in LO that will do the job. Hopefully, as LO evolves, this issue may be addressed; until then, I will use a lookup table coupled with VLOOKUP (as pointed out by Robleyd). The essence of Lupp's suggestion is the same; however, rather than having a long formula, I would much rather Calc lookup the value from a simple, hidden table.

I will leave this post as unsolved because I could not find a simple formula to address the issue - which was the basis of this post.
LO 6.x on Linux Mint 18.2 Cinnamon
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 489
Joined: Sun Jan 20, 2008 5:06 am

Re: Convert 3-letter Month Abbreviation to Numeric Format

Postby crusader » Wed Feb 13, 2019 3:08 pm

Thank you, Mikele: your solution works! God Bless you. I saw your post after posting mine (above).

I will mark this post as solved.
LO 6.x on Linux Mint 18.2 Cinnamon
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 489
Joined: Sun Jan 20, 2008 5:06 am

Re: [Solved] Convert 3-letter Month to Numeric Format

Postby Villeroy » Wed Feb 13, 2019 3:25 pm

LO did evolve already. They dropped all date evaluation except for the date patterns that are specified in the language options plus ISO dates YYYY-MM-DD. My problem with user defined date patterns is that they are removed when you switch to another locale which I do quite often for my "work" on this forum.

Too many ignorants complained that "1-3" evaluates to third of January in US context and first of March for the rest of the wold. Entering numeric expressions as text is a widely unknown practice and LO adjusts to the dumbest user. This has even higher precedent than MS compatibility. Nevertheless, I have adjusted myself to LO because AOO does not work anymore with database forms on my Linux system and because our employees prefer the green one over the blue one. Oh, some of the changes and new features are really nice.
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Convert 3-letter Month to Numeric Format

Postby crusader » Fri Feb 15, 2019 1:16 am

LO continues to evolve. God Bless LO developers and supporters.
LO 6.x on Linux Mint 18.2 Cinnamon
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 489
Joined: Sun Jan 20, 2008 5:06 am

Re: [Solved] Convert 3-letter Month to Numeric Format

Postby Villeroy » Fri Feb 15, 2019 1:33 am

Evolving is better than not evolving even if some things change for the worse. I can use all my documents with LO but not with AOO, and this has nothing to do with features that exist in LO only. AOO has problems with the windows/display/widgets/whatever in recent Ubuntu versions.
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests