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.
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 .
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.
1 Like
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? 
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. 
{… using a different spreadsheet software…}
- What’s “standard XLSX format”? Even MS don’t know, I was told.
- 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.
- 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.
- Again: If you want software acting exactly like XYZ, you need to use exactly XYZ on exactly the same system.
- 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.
-
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) ornumberorformula, and -if formula- the result type can only be one of the other two orerror. - 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
numberis represented by a 64-bit portion in RAM following the IEEE 754 Double specification. - 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.
- (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. - 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.
- 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.
- 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.
- See also attached example (accepting somehow inefficient means in this case).
disask69939ResponsibleDateTimeHandling1.ods (30.6 KB)
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.
- The relevant issue here is that there isn’t right or wrong where no clear specification is existing.
- If you think there’s an ISO-approved standard to the effect that the handling of “our case” by the software you named is correct, and the way LibO Calc does it is not, please show me the way to find it.
- Any commercial player in the field surely isn’t authorized to create standards.
- The more general issue is that the common part of the as-if-standard (that’s using a single Double value for a combined representation of date and time) is bad.
- Do you remember the “y2k” crisis? We (“humanity”) should have learnt a lection: Use the needed amount of storage/paper/RAM/whatever for the information you want/need to represent. Never prefer a silly concept of “efficiency” or “de-facto-standards” over reliability and correctness. Otherwise you can’t ever have unambiguous communication - which may be a communication with yourself bridging a few years.
- Once again: Don’t deny the reliabilities of the user.
To calm the overheated minds: I remember a technical article that revealed calculation errors. The report dealt with deviations from the calculated course in space travel. Conclusion: one does not always allocate enough register cells (memory for binary values), and divisions lead to decimal places that no longer have space and are thus suppressed. Conclusion something like this: multiply by a suitable value and divide by 10^n, which corresponds to a bit-wise shift. This shifting is not a mathematical operation with conditional rounding tolerances. This is because the number of memory cells in the “accumulators” is always limited by the specified type. Even if the initial deviation is insignificant, it adds up after many operations. Something like this can be quickly localized and corrected using pen and paper, but not in an electronic system with a digital system!
The thread where you added your comment has not received messages in nearly 3 years (I don’t count the additional “answer” below wich is maybe " problem vanished"). Do you think it needs more “calming”?
1 Like
This isn’t actually a soluition, of course, but having commented badly on the issue in more than one place, I want to apologize and get this clear:
I now think I was wrong concerning the relevant parts of my comments.
The current state of my understanding you find in my comment #4 on the related bug report tdf#139932. Unfortunately I don’t feel capable of analyzing the original code of LibO.
As of 10/14/2024 and with the version detailed below, I have opened the initial sample and it shows correctly(?)
Version: 24.8.1.2 (X86_64) / LibreOffice Community
Build ID: 87fa9aec1a63e70835390b81c40bb8993f1d4ff6
CPU threads: 16; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: es-ES
Calc: CL threaded
1 Like
The sample file https://ask.libreoffice.org/uploads/short-url/mXuxqtNozKoIsBUvQQu0uoThHtZ.xlsx does not show the rounding effect with current “still” release:
Version: 24.2.6.2 (X86_64) / LibreOffice Community
Build ID: ef66aa7e36a1bb8e65bfbc63aba53045a14d0871
CPU threads: 4; OS: Linux 5.15; UI render: Skia/Raster; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded
2 Likes
In 24.8.2.1 after Hard Recalculate, if the date/time is formatted as a number with sufficient decimal places it can be seen that by cell A721, the underlying number is slightly more accurate than the original spreadsheet. Previous versions showed exactly the same underlying number for Excel and Calc but they displayed a different time.
compare the two algorithms for hourly grading in TAB-1 and TAB-2:
01_LO-CALC_ZählerStände nach DATUM u UHRZEiT auswerten_081903.ods (54.4 KB)