Page 1 of 1

[Solved] Convert 3-letter Month to Numeric Format

PostPosted: Wed Feb 13, 2019 3:04 am
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!

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 3:10 am
by robleyd
Would a lookup table and VLOOKUP work for you?

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 3:32 am
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.

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 3:44 am
by Villeroy
You have to build a formula from inside out. =A2&1 gives what? Jan1. Jan1 is not a date by any means.

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 4:22 am
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))

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 8:10 am
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

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 12:24 pm
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   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.

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 12:57 pm
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.

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 3:00 pm
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   Expand viewCollapse view
=MONTH(DATEVALUE(A2&"-1"))
solves the problem?

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 3:02 pm
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.

Re: Convert 3-letter Month Abbreviation to Numeric Format

PostPosted: Wed Feb 13, 2019 3:08 pm
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.

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

PostPosted: Wed Feb 13, 2019 3:25 pm
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.

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

PostPosted: Fri Feb 15, 2019 1:16 am
by crusader
LO continues to evolve. God Bless LO developers and supporters.

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

PostPosted: Fri Feb 15, 2019 1:33 am
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.