Portability of localized date formats in functions like TEXT()?

English
, ,
#1

I have a date value in cell A1, for example “2024-06-30”.
Another cell should display “Date: 30.06.2024”.

With an English locale, I would use:
=CONCAT("Date: "; TEXT(A1; "DD.MM.YYYY"))
But my LO uses the German locale by default, so I have to use:
=CONCAT("Date: "; TEXT(A1; "TT.MM.JJJJ"))
instead.

Can I safely share this document with co-workers whose LO may use a different locale?
Since the document was created and saved with the German locale, will the “TT.MM.JJJJ” format work as intended?
Which format string would a person using an English locale see in the formula?

Personally, I think it’s very unfortunate that date formats like D or Y were ever localized.

edit: here’s a example file for illustration: test-format-l10n.ods (9.7 KB)

edit 2: adjusted sample document to better match the formats mentioned in the text: test-format-l10n-2.ods (10.0 KB)

#2

Yes and No - as often.
.
Internally the date is stored as a number of days since end of 1899. So this will stay consistent for any locale, if you use ods-files.
.
But, if you set a german format all recipents will see this unless they change formatting themself.
.

Maybe that evolved before we used computers … But we can use iso date yyyy-mm-dd

#3

If your original date is really a date and not just text then it is just a matter of entering =A1 on the target cell and formatting it to display the date as you want in the language you want.

If your original date is text then convert it to a date, see Faq/Calc/How to convert number text to numeric data - The Document Foundation Wiki

#4

I’m not worried about the cell containing the date - I’m worried about the TEXT() function and its localized format string. I’ll add an example document, maybe that will help explain my issue.

#5

can we ?

Number Format Codes

image
image778×323 74.3 KB

so, the OP is how to manipulate these “differences” with functions,

and the status probably not really changed for years :innocent:

#6

Yes we can! Otherwise the Formula:

=TEXT(A1;"YYYY-MM-DD")

…would not work with me:

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.6; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Debian package version: 4:7.4.7-1+deb12u2
Calc: threaded
#7

Interesting. That special case works, but "DD.MM.YYYY" produces “DD.06.YYYY”.

#8

Indeed, thats stupid … iso-format works but not other formats !

#9

also dann, what can the sentence in Help mean ? :thinking:

When you switch to a German locale, you must use JJJJ instead.

#10

Apparently, Excel users face the same exact problem, with solutions ranging from changing the desktop’s locale every time just to appease some spreadsheet formulas, and VBA macros to identify the current locale, to a duck-typing kind of locale detection based on localized month names.

It’s a ridiculous situation.

#11

See my comment above yours… at least the implementation is inconsistent!

#12

Indeed, that is awful. And indeed, that is the history, when users expected that the formulas that they used in one spreadsheet application works the same way in another application (so when they used JJJJ there, they expect JJJJ to work here).

But you have a way to force the locale on any cell (e.g., using styles), and then your cells will not depend on the program locale. Just set the incorrectly named “Language” on Numbers tab from an “Automatic” entry (which means that it would follow what is set globally in program), to any explicit value (even the same language, just without the “Automatic” suffix).

image
image972×839 99.7 KB

IIRC, Excel lacks this ability. You can’t set a locale for a cell, to force a specific syntax. This is important when sharing files: it will work only with ODS.

2 Likes
#13

I had actually tried that before, but I must have made a mistake somewhere…
I can confirm that this works as desired:

LO-cell-locale-test

Thank you!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.