Bug 145131 - Wrong operations MINUTE() function
Summary: Wrong operations MINUTE() function
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.2.1.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2021-10-14 14:20 UTC by Jerzy Moruś
Modified: 2022-05-02 13:17 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
An example of wrong operation of the MINUTE() function (16.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-10-14 14:20 UTC, Jerzy Moruś
Details
just some comments and samples what to consider doing time calculations in calc (27.57 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-10-20 11:24 UTC, b.
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jerzy Moruś 2021-10-14 14:20:22 UTC
Created attachment 175740 [details]
An example of wrong operation of the MINUTE() function

A problem with the proper calculation of the MINUTE () function emerged accidentally.
I present it in the attached file. When the function determines the number of minutes from the time difference, the result is in some cases a different result than the difference shown when "HH: MM" is formatted.
I have this problem in 32 and 64 bit versions on Windows 10
Comment 1 m.a.riosv 2021-10-14 17:41:09 UTC
Looks like a rounding issue to calculate the minutes.
In G2 =MINUTE(ROUNDUP(B2-A2;6))
seems to work fine.

Please, @Eike can you take a look.
Comment 2 Jerzy Moruś 2021-10-14 21:23:45 UTC
Interestingly, if I use the MINUTE(D2) function (D2 contains the formula B2-A2), I get the correct result. If I use the MINUTE(B2-A2) function, some results are incorrect.
Apache OpenOffice (4.1.11) does not have this problem.
Comment 3 Alex Sims 2021-10-20 00:34:26 UTC
This issue is documented at https://wiki.documentfoundation.org/Faq/Calc/Accuracy

In the example linked 2020-04-13 12:18:00 - 2020-04-13 12:08:00 is calculated into cell A3 and then

"Cell A3 will show 00:09:59.999999 instead of expected 00:10:00.000000 if formatted using [HH]:MM:SS.000000 format string. This happens despite only whole numbers of hours and minutes were used, because internally, any time is a fraction of a day, 12:00 (noon) being represented as 0.5. The data in A1 is represented internally as 43934.5125, and in A2 as 43934.5055555555591126903891563 (which is not exact representation of the entered datetime, which would be 43934.505555555555555555...). Their subtraction results in 0.00694444443287037, a value slightly less than expected 0.00694444444444..., which is 10 minutes."

So its a more fundamental problem with using a binary number to represent fractions of a day which can't be done exactly.
Comment 4 Alex Sims 2021-10-20 00:45:56 UTC
This is yet another example of issues manipulating dates stored as reals.

*** This bug has been marked as a duplicate of bug 125580 ***
Comment 5 b. 2021-10-20 11:24:12 UTC
Created attachment 175847 [details]
just some comments and samples what to consider doing time calculations in calc

-- see in the sheet --
Comment 6 m.a.riosv 2021-10-20 14:38:45 UTC
I don't think it is a duplicate, here there issue is with MINUTE() function calculation not about sum time values.

I think using the same formula used to calculate the minutes in time format, could solve the problem, as the cell format gives accurate results.

--------------

(In reply to b. from comment #5)
> .....

With
=MINUTE(ROUNDUP(B15-A15;6)) gives a good result for the six cases.

Apparently on the sixth decimal, periodic decimals for minutes values begin, and rounding up to 6th decimal before the function calculation the minutes seems to get good results.