[Solved] Convert 3-letter Month to Numeric Format

[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!

[robleyd]

Would a lookup table and VLOOKUP work for you?

[crusader]

Thank you for the quick response, Robleyd.

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.

[Villeroy]

You have to build a formula from inside out. =A2&1 gives what? Jan1. Jan1 is not a date by any means.

[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))

[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

[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.

[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.

[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?

[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.

[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.

[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.

[crusader]

LO continues to evolve. God Bless LO developers and supporters.

[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.