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
- 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">
<text:p>06/27/18</text:p>
</table:table-cell>

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: 6.2.0.0.alpha0+ (x64)
Build ID: ae5d6e44ce2310ef3ec4b0d12fe2d0005567a1ef
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: de-DE (de_DE); Calc: threaded

workaround:
- 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:

https://cgit.freedesktop.org/libreoffice/core/commit/?id=57efd69c22e2c6f5cb4d057345644b6e07a62d48

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

@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.

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.