Custom date format with [$-xxx] language code while ex/im-port .xls (97)

[PrvosenkaJarni]

Hi there,

I’m struggling with the issue as per $subj. When saving the file as .ods the cell format is preserved including the language definition (both selecting the language from drop down list and explicit declaration seem to work fine). But once the file is exported as .xls or .xlsx (I need to send it to someone else who uses MSO) this is gone.

Is this a limitation of .xls(x) format itself or possible compatibility issue?

When using the same explicit format with TEXT() function it works flawlessly.

Steps to reproduce:

• Let’s say A1 contains number 51196.5242592593 and B1 contains formula ‘=A1’.
• Now format A1 cell using ‘[$-1809]MMMM YYYY;@’ format definition and B1 using ‘[$-83c]MMMM YYYY;@’.
• You should get ‘March 2020’ in A1 and ‘Márta 2020’ in B1.
• Now set A2 to formula ‘=TEXT(A1;"[$-1809]MMMM YYYY;@")’ and B2 to ‘=TEXT(B1;"[$-83c]MMMM YYYY;@")’
• You should get the same line.
• Save as .ods
• Save as .xls (or .xlsx)
• Close file or exit LO
• Open the .ods file - you should see the same as before
• Open the .xls file - the formatting is lost and reset to system default language in line 1.

Attaching another example .ods file (just export as .xls and reopen to trigger the issue) - lcid_xls.ods

I don’t have access to MS Excel myself so can’t really confirm whether the issue happens during export or import.

Confirmed in these LO versions:

# Kubuntu 16.04 Xenial
libreoffice-calc    1:5.1.6~rc2-0ubuntu1~xenial10    amd64
# KDE Neon (based on 18.04)
libreoffice-calc    1:6.0.7-0ubuntu0.18.04.10        amd64

Thanks everyone!
PJ.

[Hrbrgr]

Open the .xls file - the formatting is lost and reset to system default language in line 1.

Basically:

Always create and save your files in LibreOffice and save them in ODF format (ODT, ODS, etc.).
Always keep these files as their source. If you need other formats for distribution to partners, you can open an ODF file and save and distribute another format with ″ Save as… ″.

This way, you always have working files available in your system environment.

See:
https://wiki.documentfoundation.org/Faq/General/118

========================

Please report the behavior as a bug in Bugzilla .

Please announce the link from the bug here.

To do this, edit your initial question. Thank you.

[erAck]

Not necessary anymore as already implemented, see tdf#73063 and tdf#128649.

[Hrbrgr]

@erAck , thanks.

[erAck]

Your LibreOffice version does not support saving the number format’s locale information to Excel file formats yet, that was implemented as of LibreOffice 6.2 but had a bug that sometimes made Excel trip over currency formats with a duplicated locale designator, which was fixed for LibreOffice 6.3.4. Best use LibreOffice 6.3.6 or later.

[PrvosenkaJarni]

Thank you I will try newer build!