When looking at an old bug report (https://bugs.documentfoundation.org/show_bug.cgi?id=33689) i found this: It is possible to have DATE fields in different locales in 1 spreadsheet, simply by pressing CTRL+1 , and choosing a different language. Doing this it's possible to create a sheet with these two formulas (i.e.): 1) =TEXT(A2;"DD-MM-YYYY") 2) =TEXT(A2;"DD-MM-JJJJ") The first with Language set to 'English', the second with language set to 'Dutch' Above works correct in Openoffice, but saving this sheet as XLSX changes the format of the English formatted date to '17-03-YYYY' This can be shown by opening this file in Excel, of in OpenOffice, which shows the date as "17-03-YYYY" (when the settings of the locale is set to Dutch)
I don't understand how to repro, so please attach an example ODS. Set to NEEDINFO. Change back to UNCONFIRMED after you have provided the document.
With LO and Gnumeric there is no problem to open the file saved as xlsx with the two different formatting's. But with AOO it is and maybe also with exel. Version: 6.1.0.0.alpha0+ Build ID: 953a5cb1c8c750df24ed0316349a5916c384296d CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group
Created attachment 140675 [details] formatting date with different locale's
(In reply to Xavier Van Wijmeersch from comment #3) > Created attachment 140675 [details] > formatting date with different locale's Can you include an ODS?
Created attachment 140677 [details] ODS sample
NEW per Xavier's confirmation.
LibreOffice now accepts English format codes in the few locales that use localized format codes so that a document using the TEXT() function can be loaded in different locales. There's nothing we can do about other products if they expect and only accept a localized format code.
"LibreOffice now accepts English format codes in the few locales ..." It should also tak info account this info when creating an export. The export format is not 'defined' by LibreOffice, but in this case by someone else, so LO should follow that definition, and not it's own.
(In reply to Luuk from comment #8) > "LibreOffice now accepts English format codes in the few locales ..." > > It should also tak info account this info when creating an export. > > The export format is not 'defined' by LibreOffice, but in this case by > someone else, so LO should follow that definition, and not it's own. How should this be taken into account? Please give concrete details. Such as "When saving as XLSX, change format codes to match locale" or "When saving in any file format, change format codes to match locale".
The format code passed to the TEXT() function is a string, which can be constant or made up of function calls or concatenations. It is user data. There is no solution to "consider / take into account during export". It isn't known in which environment the file will be opened next. Also, other applications may not have the feature that the format code is tried to be interpreted with the locale set as number format locale, so a localized format code may or may not work. Use of the TEXT() function in different locales is fragile. The attached document btw works only in one locale, the cell's B3 locale is Default and not set to Dutch, so when recalculated it displays 17-03-JJJJ in an en-US locale. It doesn't help to reopen this. Unless someone comes up with a .xlsx document created by Excel that works for all locales I'm closing this again.