Calc erroneously "rounds" the minutes in this case. (¿?)

English
, , ,
#1

Calc erroneously “rounds” the minutes in this case.

Casuistry:

  • Excel file created in “Numbers” (Mac)
    Datetime column with the format: DD MMM YYYYY HH:MM (01/09/2015 22:00:00), where each field is a different time of the same day, the time is always in point.

  • Same file, without modifying anything, opened in Libre Office Calc (Windows 10 locale es_ES):
    The fields of the first day are displayed correctly, after 00:00, it starts displaying the hours incorrectly, for example: 00:59, 01:59, etc…

  • Same file, without changing anything, opened in Google Drive: all fields are displayed correctly.

I have no idea why, but I think it is quite serious that, without showing any warning, when opening an Excel file in Calc, it shows data that are not.

Any ideas or suggestions?

Update: I’ve upgraded to version “LibreOffice_7.2.2_Win_x64.msi” but the alleged error persists.

2021-11-01_12h43_45
2021-11-01_12h43_45664×633 78.7 KB

example_data.xlsx (11.6 KB)

1 Like
#2

Could you upload the problematic file (or file fragment) in spreadsheet format?

#3

Post edited with requested data.

#4

Calc (like Excel) is the world of approximate computing. Time (1; 0; 0) is a number 1/24 that cannot be exactly represented in the binary number system used in the program.
Like a digital clock, Calc does not round the time when it applies the appropriate number format to the cell. If you need to round off the time to seconds, then you can do this in a formula, for example, for cell A3:

=MROUND(A2+TIME(1;0;0); 1/24/3600)
1 Like
#5

The problem in this case is that Calc does not display the same information as at least two other office programs, without giving any warning or showing any sign that for X or J reason(s) the information displayed may differ from the original.
I think, frankly, that this is quite serious on many levels.

If in Numbers and Drive I see 02 Sep 2021 14:00:00, it makes no sense at all that in Calc I see 02 Sep 2021 13:59:59 because, basically, that information is wrong and can lead to really serious problems.

#6

Open your file in Excel and enter the formula in cell B100:

=A100 - (DATE(2015;9;5) + TIME(2;0;0))

Format the cell B100 with 12 decimal places. You will see the result -0.000000000240, not 0.

From the point of view of Calc, the time has not yet arrived at 2:00:00.

1 Like
#7

There recently was a similar question (didn’t find it now) and there is the bug report tdf#139932
As already @sokol92 told, this is about rounding effects that can’t be generally avoided for fundamental reasons, except by using a specialized time-type for all calculations concerning time internally and in the cells as well. (This would break the usability of 1/24 for 1 h.).
I don’t know precise standards binding any software working with TimeOfDay formats, but any software I ever used tried to do it the way watches and public clocks do: The running time always is rounded down to the nearest value representable in the chosen resolution (may be hours, minutes, or seconds).
This isn’t directly compatible with the ways rounding is done generally in spreadsheets.
The numeric value returned for TIME(1;0;0) or for TIMEVALUE(“1:00:00”) converted to decimal is 4.166666666666670E-2. That’s simply the effect of how IEEE754 Double and its rounding for the display are treated. As you see, the numeric value of an hour by spreadsheet standards is not exactly representable in decimal. It’s also not exactly representable in dyadic because the divisor 24 (1/24 day for the hour) is containing the prime factor 3.
Well, rounding errors accumulate if a value containing one is added again and again. In your example it accumulated sufficiently -not to a minute, but above the rounding limit of about 1E-16) in 24 steps to get large enough to afflict the display.
If a software avoids such an effect, it needs to change the rounding. Whatever way it chooses to do so, it cant’ become “correct”, but only “better accepted” by hide the effect for some additional cases - most likely needing to accept additional flaws in different situations which they hope users won’t find.
Spreadsheet software also may use a specialized numeric type for time values in the background and for all calculations with time values accepting related storage and RAM inefficiency, and…

Anyway: If you need software acting exactly as a specific version of a different brand does, you ned to use exactly that verion of that brand.

3 Likes
#8

Given A2 the first hour, in A3 type: =MROUND(A2+1/24;1/24).

#11

I Thanks for the explanation!
I can only say that everything indicates that it is an error, I have tried a multitude of excel viewers to confirm it, all of them show it well except Libre Office Calc.
Even in Open Office it displays correctly.
It is worth remembering that the file is generated by a third program that creates the column using its
image
formula, all but Libre Office interpret it correctly.

#12

and on the other hand, if we look at the times 00:00:00, Libre Office Calc, in some cases, shows the time correctly, and then returns to the error of “interpretation” of the original formula.
image

#15

It is not a question of views, it is a question of the reliability of the data being shown.
Without any warning of this possible deviation, the fact is that user A is issuing information that user B will receive and view erroneously. This type of “features” in general-purpose software has no place.

1 Like
#20

Take a look at Feature_Comparison:LibreOffice-_Microsoft_Office.

#23

I just tried the same test on Google Sheets - wow, it gives the correct answer - 0 minutes - no round off error. I’m gobsmacked.

#24

I am with you - having worked in aerospace 25 years and having been responsible for the operation of many real time and analysis systems, an error like this is unacceptable.

A simple example. Format a column as a 24 hour time HH:MM (e.g. 13:37). Then, in the first cell, enter 00:00. Is lower cells, subtract 02:00 from the cell above. In the 8th cell below the round off error produces 7:59 instead of 08:00. I am sorry, regardless of how this is implemented, math still requires 0 - 0 = 0.

So, now, for my personal use, I cannot use TIME format, but I have to use a text format and write a macro to subtract minutes and seconds individually to get proper results. However, for someone who is working across platforms and distributed systems, this is a real problem.

#25

Lol, the mention of experience in aerospace used as a proof of any knowledge in the area, in this world where aerospace is affected by this, and the actual advise is to account for it in the applications created for the industry (so by the respective specialists), looks funny… :wink:

2 Likes
#26

Are you spreading conspiracy myths? :wink:

BTW:
You can’t fight misconceptions pointing out facts if people aren’t listening.
Maybe you are actually better off with a joke - and a different philosophy.

Truth:
Flight heights can’t be expressed in SI units!!!
Fact:
The metre convention was signed in 1875. The USA joined it in 1878, the UK in 1884. The first powered flight took place in (about?) 1901.

Truth:
M/D/YYYY H:MM:SS is the date-time-format to be used!!!
Exceptions can only be accepted for a different format that is proven to be as bad or worse, and suitable to create funny(?) misunderstandings.
Fact:
ISO 8601 is the negotiated standard. In wide-range communication UTC must be used. For special missions a scientific approach is needed.

Truth
Screen sizes can be expressed by a single value: the diagonal in inches!!! 2D geometry with its “Pythagorean theorem” is just another myth. Format 16"x9" '=' 18" and 14"x11" '=' 18" are both true!!!
More than 35 years of using TV and computer monitors tell me so.
Fact:
No reasonable standard.

#27

I did not expect such a “I don’t give a shit” response from LibreOffice. Yes, errors were made in our software as it is in all large software packages. The difference here seems to be that we worked hard to eliminate them through extensive specifications, interface documents, peer reviews and testing and when we became aware of them we made every effort to correct them. Repeated subtracting zero from a number resulting in a roundoff error is not OK.

#28

You need to realize that different problems need different trade-offs. What is good (and basically vital) for aerospace, is not OK at all for a spreadsheet software like Calc. You can sacrifice speed for accuracy in on-board specialized computer (and can increate its specs, if its performance is an issue for the task), but the performance is much more important in general use of spreadsheets, and the small rounding errors are less so - and this trade-off is well-known and documented.

Additionally, we do implement better algorithms when possible (e.g., Neumaier summation (tdf#137679), or improvement in duration calculations recently(https://gerrit.libreoffice.org/c/core/+/153202 and its many follow-ups)). It’s just the statements like you did are not correct - they show lack of understanding, that the world is not so simple as some want it to be.

#32

Hi @cibernicola, I opened the file in version 24.8.2.1, in Win.10, it’s normal.

cibemicola

#9

In A3 type: =A$2+(ROW()-2)/24 and fill down as needed.

Tested for 720 rows, LibreOffice 7.0.6.2, OS Linux 4.12.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.