[Solved] Convert 3-letter Month to Numeric Format

Discuss the spreadsheet application
Post Reply
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

[Solved] Convert 3-letter Month to Numeric Format

Post by crusader »

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 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by robleyd »

Would a lookup table and VLOOKUP work for you?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by crusader »

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 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by crusader »

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 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by Lupp »

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

=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 137 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by mikele »

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

=MONTH(DATEVALUE(A2&"-1"))
solves the problem?
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by crusader »

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 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: Convert 3-letter Month Abbreviation to Numeric Format

Post by crusader »

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 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

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

Post by crusader »

LO continues to evolve. God Bless LO developers and supporters.
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply