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.

#20

Take a look at Feature_Comparison:LibreOffice-_Microsoft_Office.

#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.

#10

The problem is not that Calc does it well or not (with its formulas). The problem is that when Calc opens a file already made, but with another program, it shows wrong information.

#13

The information should be the numeric value returned for the formula in a Cell. (Can also be made a formula result using the TEXT() function.)

I haven’t any Excel at hand, and most contributors here also won’t. Thus we can’t get any progress.
I would need a chance to directly compare the handling of the file an of experimental cases by both the softwares for a final comparison.

That both applications need to round is undoubtable. If one avoids additional downrounding for DateTime values, and this way an unwanted superimposition with the general downrounding for time formats, this may result in an unwanted behaviour in a different place I would like to look for.

And: As far as the rounding isn’t done by the FPU, applications may use different rounding algorithms anyway. We should probably both study some postings about the topic like those found by rounding algorithms at DuckDuckGo .

#14

Yes, that there may be different algorithms for rounding affects, that is a fact, but the problem, what I honestly think is a serious problem, is that if user A saves a file in a standard XLSX format, with a time type data, the user should see exactly the same data. If the algorithms are different and it is impossible for Libre Office to do otherwise, it should at least, in some way, warn that the affected column has been calculated and therefore may differ from the ones shown. Users are not going to wonder whether the algorithm used by one or the other approximates in one way or the other, and in fact, it makes no sense at all to wonder about this in general-purpose software.

On the other hand, I think it is interesting to note that Libre Office, in the attached file, does round correctly in the oo:oo:oo hours and rounds the first 24 hours correctly.

#16

Let’s go from the other side.
Enter 21:59:59 in cell A1 and set the cell format for hours and minutes (no seconds). We see the value 21:59. Conclusion: time is not rounded up. If you set the time format in hours, it will display 21. This is true for both Calc and Excel.

Now we open your file in Excel. Let’s enter the formula in cell B100:

= A100 < (DATE(2015,9,5) + TIME(2,0,0))

Its value is TRUE. Hence the date in A100 is less than 9/5/2015 2:00:00 (that’s Excel says, not Calc). According to the conclusion in the first part of this message, times are not rounded up. So what should we see in cell A100? :slightly_smiling_face:

#17

We could go on ad infinitum, but the fact is, what ends up happening is that out of four spreadsheet programs, three show one piece of information, the fourth shows another piece of information.
The user who has generated the data and saved it using his spreadsheet program has obviously done so assuming that the receiver is going to see the same data and not another.
User B, on opening the file, observes the column of dates, without knowing that what he is seeing is not what user A wanted him to see.
Both user A and B (here we must add that B is, in reality, N number of users) do not know that they are working on different values because “nobody” (software) along the way is warning them about anything at all.
This is not taking the user into account, whichever way you look at it, let’s remember, moreover, that we are talking about general-purpose programmes, which means that their adaptation to the huge number of casuistry is inherent to their very existence. :upside_down_face:

#18

{… using a different spreadsheet software…}

  1. What’s “standard XLSX format”? Even MS don’t know, I was told.
  2. The (common) term “file format” is gravely misleading. It is suggesting there is one original “thing-in-itself”, and the “format” is just one of a few different ways to wrap it up. Actually the “things” different file standards can wrap are different, and the “translations” never can be perfect.
  3. Again I would assume what you actually expect is that MS programs define the “mandatory standards”, and everything else must be a kind of clone. That’s wrong - and can’t be implemented.
  4. Again: If you want software acting exactly like XYZ, you need to use exactly XYZ on exactly the same system.
  5. In your example file there only is (excluding the heading) one single cell with data. That’s cell A2 of the only sheet. All the cells below don’t contain data but formulas.
  6. time type data (in fact date-time type) in the exact sense don’t exist in spreadsheets. In fact the content of any cell can only be of type string (text) or number or formula, and -if formula- the result type can only be one of the other two or error.
  7. What mostly is called “date-time-type data” (or -misleading- respective formula results) actually is number formatted in a date-time style. For a loaded document number is represented by a 64-bit portion in RAM following the IEEE 754 Double specification.
  8. Unfortunately these facts are made next to invisible to the user due to bad traditions (introduced by MS eventually). For example the formula evaluator may set a specific number format for the result cell(s) depending on the formats of referenced cells and/or a used standard function. It thus suggests the existence of a specific type hiding the facts.
  9. (Repeated) =TIME(1;0;0) can’t return a number representing exactly 1 h -which would be the fraction 1/24- in spreadsheets because this value cant be represented exactly in IEEE 754.
  10. In addition the representing dyadic fraction must be shifted right when adding the integer for the date. This currently reduces precision roughly ba an equivalent of 5 or 6 decimal places. This will get worse in coming decades. In fact the current spreadsheet-representation of date-time is indefensibly bad. Be glad to have had an opportunity to notice this fact.
  11. Using formulas the user having designed them is responsible for their correct working in every expectable environment. If users deny responsibility for assured correctness of their formulas, we should expect a Chernobyl every few years. MS or Google never will take responsibility for anything - and TheDocumentFoundation can’t compensate for this.
  12. Regarding the infinite heaps of bad advice about how to do something “in Excel”, and the already mentioned bad traditions taking responsibility isn’t easy. The General advice applicable also in this case should be: Always round explicitly if you expect rounded results. Pretended rounding by cell formats may be a way to spreadsheet hell.
  13. See also attached example (accepting somehow inefficient means in this case).
    disask69939ResponsibleDateTimeHandling1.ods (30.6 KB)
#19

Again, we can talk about the technical side, about who is right and who is wrong. That is the theoretical part.
The fact is that a software opens a file that is indeed standard (de facto standard) and sees different information than what the source user has generated.
Again, from the office suites tested, viz:
Numbers: MAC, sample A.
Google Sheets: Browser shows A
Open Office: displays A
Libre Office: sample B
Different online viewers of XLSX files: Show A
Again, we are talking about general-purpose software, not ultra-specialised software, so it is more than obvious that it has to deal with this type of problem, in one way or another.
Now, if the issue is that Libre Office is right and everyone else is wrong, well, I’m not getting into it, that kind of debate tends to go nowhere.
If we think about what the software is, the basic work it is supposed to do, I think it is radically obvious that this circumstance should be managed and the user should be warned in one way or another, both by Libre Office and by the rest.

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