Bug 149898 - Calculating datetime differences - different results depending on number format
Summary: Calculating datetime differences - different results depending on number format
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.0.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Number-Format
  Show dependency tree / graph
 
Reported: 2022-07-07 13:46 UTC by cpohle
Modified: 2023-04-24 16:27 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:

Attachments
Example spreadsheet (15.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-07-07 13:47 UTC, cpohle 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description cpohle 2022-07-07 13:46:40 UTC 
Description:
When calculating the difference between two datetime values in Calc, the result is different depending on whether the numbers are formatted as date/time (correct) or decimal (wrong result). See the attached example.

Steps to Reproduce:
See the spreadsheet attached.

Actual Results:
Caculation result depends on number format.

Expected Results:
Caculation result must not depend on number format.


Reproducible: Always


User Profile Reset: No



Additional Info:
Verified on both Windows with version 7.0.6.2 and macOS with version 7.3.4.2
Comment 1 cpohle 2022-07-07 13:47:14 UTC 
Created attachment 181155 [details]
Example spreadsheet
Comment 2 Mike Kaganski 2022-07-07 14:46:44 UTC 
Repro using Version: 7.3.5.1 (x64) / LibreOffice Community
Build ID: d56c1c78db15939340c3db8ee3b6667832313d23
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL

Doing hard recalc (Ctrl+Shift+F9) does not change the result. Cloning the formats from one row to the other and hard-recalc changes the calculation result.
The document has "Precision as shown" *unset*.

This is not reproducible using Version: 6.0.0.3 (x64)
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765
CPU threads: 12; OS: Windows 10.0; UI render: GL; 
Locale: ru-RU (ru_RU); Calc: CL

but repro using Version: 6.3.0.4 (x64)
Build ID: 057fc023c990d676a43019934386b85b21a9ee99
CPU threads: 12; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: CL

It is different using Version: 6.2.0.3 (x64)
Build ID: 98c6a8a1c6c7b144ce3cc729e34964b47ce25d62
CPU threads: 12; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: CL

in the latter, hard recalc puts 0:29 to the both cells.
Comment 3 Mike Kaganski 2022-07-07 17:39:01 UTC 
The change in 6.3 happened after commit 85c0521f01f5c726e9f754b3175a550121e566c8.
Comment 4 m_a_riosv 2022-07-07 18:03:09 UTC 
Something to do with tdf#127334?
https://bugs.documentfoundation.org/show_bug.cgi?id=127334#c1
Comment 5 Eike Rathke 2023-04-24 16:27:10 UTC 
So what do you expect? We introduced some special handling for subtracting date+time formatted values to yield better results, and these 44749.3333333333 and 44749.3541666667 are not exact (neither decimal nor binary floating point) numbers, and displaying the result from this calculation in time format is slightly off. Yes.