Hi. Today is November 13. I have Libreoffice Calc document and in it in the cell A3 the value is =TODAY(). I need to put a formula to the cell B3 which calculates the following string:
Today is November 13, 2024 (or 13 listopada 2024 in Polish)
Is this possible without hardcoding month names in the formula?
CHOOSE(MONTH(A3); "stycznia",...)
Is it possible to just set 2 different languages in 2 cells, use format MMMM and then contatenate the cell formatted values instead of using TEXT()?
Nice idea and it works to “show” the date you like, so if it is ok to have this values in two cells side by side, you can follow this route.
.
The problem is: When you concat the cells it will concat the actual value (numeric representations of the date (45364 in your example) , not the shown value.
.
I tried to use =TEXT but this gave my language, not the language of the cell (looked like concatenating march & mars to MärzMärz).
.
So I used the NatNUM12 modifier and =TEXT to get the same result as Mike.
His solutions needs only one cell/formula, so I’d usually prefer this.
But, if you wish to change the language from time to time, it may be easier to go through format cells than looking up language codes.
“My” formula is then =TEXT(A3;"[NatNum12]MMMM")
Not quite sure what you mean. =TEXT(A3;"MMMM") would follow the current cell’s number locale; so in two cells with this identical formula, but with different locales, the resulting strings would be different - and will allow concatenation in a third cell.
Note that [NatNum12] has quite significant overhead (implemented in Python, so would incur transferring data between the C++ code and Python, and interpreting there I obviously misremembered, or Laszlo has changed it since then; the only performance penalty is because of heavy use of regular expressions), so I’d avoid its use when possible (not saying it’s “bad” - to the contrary, I like it very much, and contributed to its implementation, what I could; just that its use should be limited to really necessary cases).
You are right. NatNum is not necessary here.
Maybe referenced the wrong cell, and as I knew how to do this with NatNum, I didn’t check again.
.
Thanks for the hint on the overhead, so nice for single dates like invoices, but we should think twice before using it on database-columns.
Yes, and it’s a good idea to use helper cells, because it avoids the usage of cryptic elements in the format code.
I demonstrate how to follow this path to get the wanted result in the attached example below. However, I can’t get myself to use the absurd US format. I chose Bulgarian therefore in the example.
disask113814_LocalizedDatFormttingAsText.ods (16.2 KB)
="Today is " & TEXT(A3;"[$-409]MMMM D, YYYY") & " (or " & TEXT(A3;"[$-415]D MMMM YYYY") & " in Polish)"
See TEXT function documentation, referencing Number Format Codes, which in turn references MS-LCID.