Bug 52510 - Calc removes trailing semicolon in custom format , breaking Excel compatibility
Summary: Calc removes trailing semicolon in custom format , breaking Excel compatibility
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: target:6.0.0 target:5.4.2
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2012-07-25 22:35 UTC by prophnovsky
Modified: 2017-09-15 19:19 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description prophnovsky 2012-07-25 22:35:53 UTC
When using user-defined formats for cells in Calc, trailing semicolons are automatically removed.  This is likely because Calc believes that the end result is incorrect; however, Excel treats the trailing semicolon as an exception rule for the format.

To reproduce this problem in Calc, do the following steps:

1. Enter zero ('0') in the first two rows of the first column of a new spreadsheet (actual cell position is irrelevant).
2. Use "Format->Cells" menu or the "Format Cells..." right-click item to do custom formatting on the cell.
3. In the category list, select "User-defined."
4. Enter a Format code of "_($* #,##0.00_);_($* (#,##0.00);"

Similarly, this can be reproduced in Excel:

1. Enter zero ('0') in the first two rows of the first column of a new spreadsheet (actual position is irrelevant).
2. Use "Format Cells" menu or the "Format Cells..." right-click item to do custom formatting on the cell.
3. In the category list, select "Custom."
4. Enter a Type of "_($* #,##0.00_);_($* (#,##0.00);"

In both examples, only enter the formatting text between the quotation marks.

Excel leaves the semicolon and formats the cell with a blank entry.  Libreoffice Calc removes the semicolon and formats the cell using the existing entries with a result of "$0.00"

The expected behavior is to simply blank out the cell if none of the entries match - the semicolon gives the exception case, similar to an else statement.
Comment 1 Sören 2012-12-26 19:13:09 UTC
Reproduced the behavior in
Version 4.0.0.0.beta2 (Build ID: 4104d660979c57e1160b5135634f732918460a0)
on Ubuntu 12.04 (x86)
Comment 2 QA Administrators 2015-01-05 17:51:26 UTC Comment hidden (obsolete)
Comment 3 Buovjaga 2015-01-20 16:34:04 UTC
Still happens.

Win 7 Pro 64-bit Version: 4.5.0.0.alpha0+
Build ID: 07e84cae983c08afdba03018413a19d01abb3006
TinderBox: Win-x86@62-TDF, Branch:MASTER, Time: 2015-01-19_06:15:38
Comment 4 QA Administrators 2016-02-21 08:35:24 UTC Comment hidden (obsolete)
Comment 5 QA Administrators 2017-03-06 15:14:53 UTC Comment hidden (obsolete)
Comment 6 Eike Rathke 2017-09-01 16:56:24 UTC
Taking.
Comment 7 Commit Notification 2017-09-04 09:20:08 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8cb3faca4b1d1d9c68faba103c6c6117853e639e

Resolves: tdf#52510 handle trailing ';' specifying empty following subformat

It will be available in 6.0.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Eike Rathke 2017-09-04 09:24:18 UTC
Pending review https://gerrit.libreoffice.org/41878 for 5-4
Comment 9 Commit Notification 2017-09-15 19:19:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8a398429cd7b8369af360def49989623176cd233&h=libreoffice-5-4

Resolves: tdf#52510 handle trailing ';' specifying empty following subformat

It will be available in 5.4.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.