Bug 139932 - DATE[VALUE](...) + TIME[VALUE](...) one second less than expected for some years and hours
Summary: DATE[VALUE](...) + TIME[VALUE](...) one second less than expected for some ye...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2021-01-26 20:50 UTC by Ian S.
Modified: 2023-11-13 18:34 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:

example for one-second-less-behaviour (88.45 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-01-26 20:50 UTC, Ian S.

Note You need to log in before you can comment on or make changes to this bug.
Description Ian S. 2021-01-26 20:50:58 UTC
Created attachment 169167 [details]
example for one-second-less-behaviour

In the attached ods you can see that for 
- a few years 
- everyday
- but only for some hours

1) DATE(<year>;1;1) evaluates correctly
2) TIME(<hour>;0;0) evaluates correctly
3) the sum of both evaluates to one second less than expected

1) DATEVALUE("01.01.<year>") evaluates correctly
2) TIMEVALUE("<hour>:00:00") evaluates correctly
3) the sum of both evaluates to one second less than expected

This might be related to #127498, but there the TIMEVALUE(...) itself is reported to be one second less whilst herein only the summations are.

Best regards
Comment 1 Louis Steinberg 2021-02-24 17:47:32 UTC
Thank you for reporting this issue.  I can confirm that version

Version: / LibreOffice Community
Build ID: f6099ecf3d29644b5008cc8f48f42f4a40986e4c
CPU threads: 4; OS: Mac OS X 10.15.7; UI render: default; VCL: osx
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

has the same behavior.  I am not sure it is a bug, however.  It looks like a rounding problem, and I do not know the details of the way dates and times are represented in LibreCalc, so I cannot really evaluate whether it is a rounding problem or not.

For what it is worth, opening the ods file with Microsoft Excel for the Mac version 16.16.18 does not show this problem.
Comment 2 Louis Steinberg 2021-02-25 20:25:06 UTC
I did a bit more digging and found https://bugs.documentfoundation.org/show_bug.cgi?id=127334  From the comments there, problems with time values caused by rounding is a known issue, with no perfect solution.

So the behavior demonstrated here is probably not a bug (for some value of "bug").
Comment 3 Xisco Faulí 2021-10-22 09:21:27 UTC
@Eike, I thought you might be interested in this issue
Comment 4 Wolfgang Jäger 2021-11-05 16:08:21 UTC
Hard to understzand.

I traced the examples
which has an even serial nuzmber (26568) relative to default nulldate.
down to the IEEE 754 Double bit-pattern 64 bit).

The patterns were identical with the exception of the one bit for the additional day in the second case (14th bit of the mantissa).
Using the code "HH:MM:SS.00000000000" value-INT(value) was formatted to 01:59:59.99999989500 in both cases.

The full date-time, however was formatted differently as the reporter described.
I can't see any possible effect of rounding or error accumulation ... explaning the experiences.
There must be a rather smart bug inside the date-time-formatting routine. 

Since date-tiume-values are generally rounded DOWN for the display, there should be a branch limiting the effect to some fraction of a second, and this way avoiding the "59.999.... s" where a full minute must be expected. However this branch must be skipped for unknown reasons in rather rare cases.  

The mentioned bit patterns (LSbit right) were
0 10000001101 1001111100100000010101010101010101010101010101010101 and
0 10000001101 1001111100100100010101010101010101010101010101010101