Description: In Calc 6.0 this calculation 11:24-08:00 gives the (correct) result 3:24:00 - in Calc 6.2 the result is 3:23:59. Attached screendumps from 6.0, 6.2 and a sample spreadsheet. Actual Results: In Calc 6.0 this calculation 11:24-08:00 gives the (correct) result 3:24:00 - in Calc 6.2 the result is 3:23:59. Expected Results: The expected result would be 3:24:00 Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 151148 [details] Sample spreadsheet
Created attachment 151149 [details] Screendump from Calc 6.0
Created attachment 151150 [details] Screendump from Calc 6.2
I can't reproduce it in Versión: 6.2.3.2 Id. de compilación: aecc05fe267cc68dde00352a451aa867b3b546ac Subprocs. CPU: 1; SO: Windows 6.1; Repres. IU: predet.; VCL: win; Configuración regional: es-ES (es_ES); Idioma de IU: es-ES Calc: threaded Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ? I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Still there in Version: 6.2.3.2 (x64) Build ID: aecc05fe267cc68dde00352a451aa867b3b546ac CPU tråde: 4; Styresystem: Windows 10.0; Gengiver af brugergrænseflade: Standard; VCL: win; Lokalisering: da-DK (da_DK); Sprog for brugergrænseflade: da-DK Calc: threaded
Since https://git.libreoffice.org/core/+/e2e47898180e547cad7ccde1e5890385d573e551. Eike: wouldn't just adding half of output resolution to the serial datetime be correct for proper rounding?
(In reply to Mike Kaganski from comment #6) > Since > https://git.libreoffice.org/core/+/e2e47898180e547cad7ccde1e5890385d573e551. I should have read the description twice before I said I couldn't reproduce it, my bad. i do confirm the commit mentioned above introduced this regression
> Eike: wouldn't just adding half of output resolution to the serial datetime > be correct for proper rounding? No, that is more or less what those commits were about, rounding up a date+time or wall clock time into the next magnitude most times is wrong, especially if 23:59:59.9 would result in 00:00:00 then (together with date the next day). The problem here is the underlying floating point value resulting from the subtraction (if the format is cleared (Ctrl+M) on A1:A2 you see the floating point values). Formatting with HH:MM:SS.000000 then shows the values 03:53:46.000002 and 03:23:59.999999 where the latter reveals the problem. The correct way would be to format the result as duration, not wall clock time, i.e. [HH]:MM:SS displays the expected 03:24:00 on A2. However, users don't use that.. not sure if we can do anything about it, maybe if for a formula the result format is automatically determined, but not if a wall clock time format is manually applied. Or maybe if we can detect a calculation result should fit into a certain wall clock time format. Investigating.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/85c0521f01f5c726e9f754b3175a550121e566c8%5E%21 Resolves: tdf#125099 round duration results in interpreter already It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/71935 for 6-2 https://gerrit.libreoffice.org/71936 for 6-2-4
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/d27ad84ec7a0aafb07d6a6152c686f4bc802f661%5E%21 Fix typed flags bitmask, tdf#125099 follow-up It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://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-2": https://git.libreoffice.org/core/+/f01e31762b02b8b896e726238eb2475c0e01ef82%5E%21 Resolves: tdf#125099 round duration results in interpreter already It will be available in 6.2.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Created attachment 165194 [details] How it looks in LibreOffice 7.1 master
Hello Eike, Mike Kaganski, In Version: 7.1.0.0.alpha0+ Build ID: 5f7cbf60b71e78d4a980ec46953c4524ec0fd80c CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded I still see 03:23:59. See screenshot. Should it be 3:24:00 instead ?
I think I have to live with this kind of problems as long as the relevant developers think they can use the concept of distinguishing between wall clock and duration time to fix some serious flaws is the specification of LibreOffice's date and time arithmetic. This idea may work for some special cases such as wall clock time - wall clock time = duration. But it will fail in cases like wall clock time + integer number * duration = wall clock time. The key point is that in this case the integer multiplier may increase some tiny round off error to such a degree that the underlying floating point arithmetic cannot correct this error any more. If the duration happens to be slightly too small then wall clock time rounding will round down which looks like an error in elementary integer arithmetic, see for example attachment 161500 [details] to bug 133389. I see no way to work around this conceptual problem by using implementation tricks. That's why I created attachment 160356 [details] for bug 127170 where I tried to describe a way how a date and time arithmetic could work. I hope that this concept contains a rounding method that is so robust that an average user will probably never notice the effects of the underlying inexact floating point representation. There is also no need to distinguish between wall clock and duration time rounding. Thus legacy spreadsheets should work. So far I am not aware of any substantial feedback concerning this proposal.
(In reply to Xisco Faulí from comment #14) > I still see 03:23:59. See screenshot. Should it be 3:24:00 instead ? You have to recalculate, i.e. on A2 hit F9.
(In reply to Eike Rathke from comment #16) > (In reply to Xisco Faulí from comment #14) > > I still see 03:23:59. See screenshot. Should it be 3:24:00 instead ? > You have to recalculate, i.e. on A2 hit F9. oh, I see, thanks a lot!
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e29b91e348be22037d9a3bcb3d6fe8ca91d5ee61 tdf#125099: sc_subsequent_filters_test: Add unittest It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Created attachment 165278 [details] Sample spreadsheet 1
Created attachment 165279 [details] Screendump from Calc 6.0.4.2
Created attachment 165280 [details] Screendump from Calc 6.4.5.2