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:
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
Column or cell displays in date format. The "General" option is ticked under Format > Number Format.
Column or cell displays in general number format.
User Profile Reset: Yes
OpenGL enabled: Yes
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
Created attachment 144456 [details]
Example file in ODS format
Created attachment 144457 [details]
Example file in XLSX format
i can confirm with your attached *.ods test file:
- open attached *.ods file
- format cells B2:B38 with Number Standard
- 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"
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: 126.96.36.199.alpha0+ (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"
Instead format as Standard, resetting the format with [Ctrl+M] seems it works.
Regression introduced by:
author Markus Mohrhard <email@example.com> 2013-05-24 22:41:44 +0200
committer Markus Mohrhard <firstname.lastname@example.org> 2013-06-02 03:25:49 +0200
commit 57efd69c22e2c6f5cb4d057345644b6e07a62d48 (patch)
parent fda007e69f16aaebe81ee7b9ac8ea4742801bb85 (diff)
remove inherited number formats, related fdo#60215
Bisected with: bibisect-42max
Adding Cc: to Markus Mohrhard
@Eike, I thought you could be interested in this issue...
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.
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.
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.
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.
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.