Bug 119509 - FORMATTING: cells with a General format are formatted to the result type and calculation not as desired in ODS (OK in XLSX)
Summary: FORMATTING: cells with a General format are formatted to the result type and ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
(earliest affected)
4.1 all versions
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Number-Format
  Show dependency treegraph
Reported: 2018-08-26 14:55 UTC by Dan Arnfield
Modified: 2019-09-13 11:47 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

Example file in ODS format (12.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-08-26 14:56 UTC, Dan Arnfield
Example file in XLSX format (5.40 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-08-26 14:57 UTC, Dan Arnfield

Note You need to log in before you can comment on or make changes to this bug.
Description Dan Arnfield 2018-08-26 14:55:43 UTC
I have a document with a column of dates and a second column which subtracts a certain number from the first to get the number of days since a certain date. For example:

A2: 06/27/2018
B2: =A2-43277

Where 43277 is the date 06/26/2018 converted to a number, so the result is 1. I have this column formatted as general numbers. When the file is saved in xlsx format it's displayed correctly when the file is closed and reopened, but if it's in ODS the column displays in the date format.

Steps to Reproduce:
1. Create a column or cell with a formula of the form [date cell] - number
2. Set its format to general number
3. Save file in ODS format and reopen the file

Actual Results:
Column or cell displays in date format. The "General" option is ticked under Format > Number Format.

Expected Results:
Column or cell displays in general number format.

Reproducible: Always

User Profile Reset: Yes

OpenGL enabled: Yes

Additional Info:
Build ID: 6.1.0-2
CPU threads: 16; OS: Linux 4.18; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); Calc: group threaded
Comment 1 Dan Arnfield 2018-08-26 14:56:32 UTC
Created attachment 144456 [details]
Example file in ODS format
Comment 2 Dan Arnfield 2018-08-26 14:57:00 UTC
Created attachment 144457 [details]
Example file in XLSX format
Comment 3 Oliver Brinzing 2018-08-27 14:02:35 UTC
i can confirm with your attached *.ods test file:

- open attached *.ods file
- format cells B2:B38 with Number Standard
- save
- reload
- B2:B38 has Date format TT.MM.JJ

test file's content.xml stored the date with "MM/DD/YY" format:
<table:table-cell table:style-name="ce1" office:value-type="date"
office:date-value="2018-06-27" calcext:value-type="date">

and i can reproduce with a new spreadsheet, if i change the 
cell language property from "Default (German)" -> Date Format 
"TT.MM.JJ" to "English (USA)" -> "MM/DD/YY" (direct formatting).

Version: (x64)
Build ID: ae5d6e44ce2310ef3ec4b0d12fe2d0005567a1ef
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: de-DE (de_DE); Calc: threaded

- select B2:B38
- context menu: "Clear Direct Formatting"
- save and reload

this will add "table:style-name="Default" to <table:table-cell/> 
elements in "content.xml"
Comment 4 m_a_riosv 2018-08-27 15:24:28 UTC
Instead format as Standard, resetting the format with [Ctrl+M] seems it works.
Comment 5 Xisco Faulí 2018-08-28 09:08:00 UTC
Regression introduced by:


author	Markus Mohrhard <markus.mohrhard@googlemail.com>	2013-05-24 22:41:44 +0200
committer	Markus Mohrhard <markus.mohrhard@googlemail.com>	2013-06-02 03:25:49 +0200
commit 57efd69c22e2c6f5cb4d057345644b6e07a62d48 (patch)
tree 768a5ed0f68f5c3a7fe2a0efe5aecca1e666953d
parent fda007e69f16aaebe81ee7b9ac8ea4742801bb85 (diff)
remove inherited number formats, related fdo#60215

Bisected with: bibisect-42max

Adding Cc: to Markus Mohrhard
Comment 6 Xisco Faulí 2018-08-28 09:08:49 UTC
@Eike, I thought you could be interested in this issue...
Comment 7 Eike Rathke 2018-08-29 09:25:42 UTC
What happens technically is that cells with a General format are formatted to the result type. The result here *is* of type date, as a number is subtracted from a date. If the result is to be forced to be displayed as number, then either apply a fixed number format such as 0, or subtract a date (which in fact that number should had represented, "43277 is the date 06/26/2018 converted to a number") by using the DATE() function or a reference to a cell with content of type date, in which case the result will be of type number.

The "if General format apply result format when loading .ods documents" was introduced because earlier version never applied the result format but only calculated it on the fly, which meant for a correct result display the entire document had to be recalculated, which isn't wanted. Also, querying the cell for the result format or type didn't work with the API and at other places because no number format was applied.

Won't change this. The only thing sensible would be to remove the "recalculate formula cells with General format when loading .ods" assuming that meanwhile (in the last 5 years) old documents have been loaded and saved once with a newer version. We'd then get bug reports from users who loaded an older document and expected a formatted result but got General instead, which we'd have to close as wontfix. Still, if such formula was re-entered the newly resulting format is applied anyway.

You can't have both. Pick one.
Comment 8 Eike Rathke 2018-08-29 09:34:32 UTC
We could also base such "don't do this anymore" on the document generator's LibreOffice version (i.e. don't if saved with 5.0 or later), though that might be somewhat fragile, but maybe better than unconditionally.
Comment 9 Dan Arnfield 2018-08-29 13:18:16 UTC
Surely part of this behavior is a bug, though? If the General format is supposed to result in a date for this formula, shouldn't it display as a date when explicitly selecting Number Format > General from the menu and not just on reopening the file?

I can confirm that a formula of the form "=A2-DATE(2018, 6, 26)" results in a number when formatted as General, both when you select that format and when you reopen the file.
Comment 10 Eike Rathke 2018-08-30 08:17:51 UTC
Applying a number format never triggers recalculation of underlying formulas, and the formula cell is also not marked as "needs to determine result type", hence applying General to a date type result will display in General format, not force back to date.
Comment 11 Dan Arnfield 2018-08-30 13:34:50 UTC
I'm still not understanding why there is a difference in behavior, or perhaps why there *should* be a difference. If recalculating General format cells on fileopen to determine result type is the right thing to do, why shouldn't it be the right thing to do when the format is explicitly applied? Or put another way, if General is supposed to represent the "auto" format for a particular formula (i.e. the result type), why should it not figure that out immediately rather than do the equivalent of "clear direct formatting" except without persistence?

I guess what I'm trying to say is that General represents two different things depending on when it's applied (explicitly applied vs fileopen). From a user perspective this breaks the principle of least surprise. But I know how to work around my particular issue now, so if you don't agree this can be closed.
Comment 12 QA Administrators 2019-09-02 09:25:03 UTC Comment hidden (obsolete)
Comment 13 Timur 2019-09-13 11:04:10 UTC
We have options here: 
- close as WontFix as explained
- convert to Enhancement and do as suggested to avoid recalc (I'm not in favor)
- convert to Documentation and add info from Comment 7 and Comment 10 there (like https://help.libreoffice.org/Calc/Calculating_With_Dates_and_Times).

One thing clear is that we don't need further testing from the reporter.

So far, I'll change to Documentation.