Description: Issue is reproduced for all input types. Steps to Reproduce: 1.Open LibreOffice Calc 2.Enter following formulas to any cell =HOUR(-0.000001) Actual Results: 24 Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: Version: 6.0.6.1 (x64) Build ID: dc68ad6c5cde3c62874b96422f2e5e8252499bad CPU threads: 4; OS: Windows 10.0; UI render: default; Locale: en-US (en_US); Calc: group
definition 6.10.10 HOUR Summary: Extracts the hour (0 through 23) from a time. Syntax: HOUR( TimeParam T ) Returns: Number Constraints: None Semantics: Extract from T the hour value, 0 through 23, as per a 24-hour clock. This is equal to: DayFraction=(T-INT(T)) Hour=INT(DayFraction*24) confirm with Version: 6.2.0.0.alpha0+ Build ID: d11313f341d1d9a9c73c736932441a89d5292e37 CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk3;
There is a rounding error in the code. For example, =HOUR("10:59:59.6") returns 11, whereas it should return 10. I will fix it.
Likewise, =MINUTE("10:20:59.6") returns 21 instead of 20, and =SECOND("10:30:59.6") return 0 instead of 59.
Also reproduced in LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c69e7266916ac1b8917477fb4eccdb9098da5792 tdf#118800 fix rounding error in Calc function HOUR, MINUTE, SECOND. It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Winfried Donkers committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=bda9288ffee552b55eed9dbf02e1204957bd4513&h=libreoffice-6-1 tdf#118800 fix rounding error in Calc function HOUR, MINUTE, SECOND. It will be available in 6.1.0.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=273b3e10eab70ebc084cb62568bd699fddfb376e Resolves: tdf#119533 reintroduce time rounding but cut, tdf#118800 follow-up It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=98cb91686901dc0133c5c23dc5658d9623dbd436 Shortcut small negative values to 0:0:0, tdf#119533 tdf#118800 follow-up It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=184aee6abd202f6f4e2c18d21f96a0d1a9fd947f&h=libreoffice-6-1 Resolves: tdf#119533 reintroduce time rounding but cut, tdf#118800 follow-up It will be available in 6.1.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-1-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=34887e7a8d36603ade1a0621d511906b826d0298&h=libreoffice-6-1-1 Resolves: tdf#119533 reintroduce time rounding but cut, tdf#118800 follow-up It will be available in 6.1.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Created attachment 147357 [details] SECOND(...) return second-1. SECOND with images in LO 6.1.3.2 and 6.0.6.2
Now if you write A2: 00:03:09 A3: 2018-10-22 11:31:44 A4: 2018-10-22 11:34:53 A5 : =A4-A3 --> result 00:03:09 The number in A2 is 0,0021875 and in A5 is 0,002187499995. I think that the error is in calculator with float numbers. In Excel 2016 is the same behaviour and function SECOND works as the old function SECOND in LO.
As I do not know if it is a matter of modifying SECOND or touching on the subtraction of dates/hours, I have created a BUG with the problem: https://bugs.documentfoundation.org/show_bug.cgi?id=121978
Created attachment 147433 [details] datetime parts comparison Document compares results for YEAR/MONTH/DAY/HOUR/MINUT/SECOND functions and cell formats for a (date)time with fractional part of the second value > 0.5. Comparison is for ODF1.2 Part2, Calc 6.1.3, Calc version with which document is opened, Excel 2016, MariadDB, Transact SQL. It shows where there are differences and may help to make changes to ODF1.2 Part 2 and/or Calc.
The correct behavior was the previous, rounding. If someone wants the exact second without rounding you can use the functions: 10:50:59,99 0 =SECOND(A1) 59 =VALUE(MID(TEXT(A1;"hh:mm:ss,0000");7;2)) With LibreOffice 6.0.7.3
See also https://issues.oasis-open.org/browse/OFFICE-4094