Description: I am importing a spread sheet file in Excel format from an Android application, and on some fields, the time captured by Calc is formatting incorrectly, and it seems to be a rounding issue with the conversion. As an example, a time of 13:00, which calidates correctly in the Android device is converted by Calc into 12:59:59, and it would seem that the value in the cell is effectively 15:59:59.9, but I cannot find a way to get it correctly rounded in Calc. The problem is that if the time is calculated using HH:MM format, the time is shown as 12:59, and then when used to calculate the elapsed time, it is showing the correct elapsed time, but due to the rounding error, it appears to be wrong by 1 minute due to the rounding issue. Steps to Reproduce: 1.Open Excel format data file transferred from Android device 2.Review data sent 3.See rounding errors on some fields Actual Results: Times imported can vary and cause apparent issues with calculated times shown in spreadsheet Expected Results: Time that should be displayed as (eg) 13:00 is displayed as 12:59, but time difference calculation has used 13:00, as the raw value appears to be 12:59:59.9, Reproducible: Sometimes User Profile Reset: No Additional Info: If needed, can provide a data file that shows this in a number of records, but I don't have enough information about the internal file structure to be sure of the diagnosis of the error. Version: 6.4.4.2 (x64) Build ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff CPU threads: 2; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; Locale: en-GB (en_GB); UI-Language: en-GB Calc: CL
Created attachment 161903 [details] imported data transfer file The file attached when opened with Calc will show a number of times as (eg) 12:59, all such cases are being rounded, and should read 13:00, the problem being that the difference between start and finish is being calculated correctly, but the display rounding is meaning that there is an apparent discrepancy between the actual times and the calculated time.
Created attachment 161904 [details] screenshot of opened data file All times shown with minute of (eg) 59 are rounded incorrectly, and should be rounded up to the next minute, which then gives the value that has been used in the calculation of elapsed time. In line 1 for example, the actual stored start time is 5:00 pm. as sebnt, no formatting has been applied to the data, it is as sent by android and imported into Calc. Changing the formatting of the cells does not remove the error lines 1, 2, 5, 7, 8, 9, 13, 16, 17, 21, 22, 23, 24, 27, 30, 32, 33, 34,35, 36, 37, 38, 43 and 44 all show examples of this error on either the start or finish time.
I can confirm with Version: 7.1.0.0.alpha0+ Build ID: 11d21b3c1f7754b5d13ae9ea88da562ec74366ff CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3 works in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
wrong bug, sorry
Created attachment 163611 [details] Comparison MSO 2010 and LibreOffice 7.1 master
(In reply to Xisco Faulí from comment #5) > Created attachment 163611 [details] > Comparison MSO 2010 and LibreOffice 7.1 master So let's set to NEW
It's as expected in 6.1.0.3, but not in 6.2.0.3. => regression Bibisected to the following commit using repo bibisect-linux-64-6.2. Adding CC: to Eike Rathke. Bug 125580 and bug 127498 have been identified as starting from the same commit. https://cgit.freedesktop.org/libreoffice/core/commit/?id=e2e47898180e547cad7ccde1e5890385d573e551 author Eike Rathke <erack@redhat.com> 2018-08-31 11:21:03 +0200 committer Eike Rathke <erack@redhat.com> 2018-08-31 12:50:52 +0200 Use tools::Time::GetClock() in number formatter for wall clock time
Created attachment 167910 [details] Sample with single time cell in XLSX Bug isn't specific to XLS, here's an XLSX saved in Excel, with the time copied over from the other sheet. Note that the numerical value (whatever's shown of it in the formula bar) is 0.708333333332121 in the buggy case, and 0.708333333333333 in the case when 5:00 PM is entered in Calc. The latter, "good" value is produced if 5:00 PM is entered in Excel, too, so it's a difference in "rounding" for display.
The minimal value for 17:00 - in Excel, and in Calc < 6.2: 0.708327546296297 - in Calc 6.2+: 0.708333333333328
think it's less a problem of calculating but of 'standard', there was a change shortly - or old and implemented shortly - that calc for time values shows digits like a digital wall clock would do, thus always rounded down, except the seconds (or fractions?), they are rounded what leads to funny effects and some discussions already ... search for 'calc' and 'wall clock',
(In reply to b. from comment #10) > except the seconds (or fractions?), they are rounded what leads to funny > effects Wrong. You are confusing with the SECOND() function.
*** Bug 143132 has been marked as a duplicate of this bug. ***
Created attachment 176154 [details] Exported from Google Calendar - Rounding Issue The attached shows a rounding issue in column E, which is the difference between columns D and C (dates/times). For example, opening the file in Calc I see 00:59 instead of 01:00 and 01:44 instead of 01:45 If I open the same file in MS Excel, the correct values are shown. ------------------------------------------------------------------------------- The file is a subset of the main file, which itself shows mostly correct values [in column E] in Calc, so I include only the incorrect values. I have a work-around that seems to work. The work around is to change the cells in column E: - from: =(D2-C2) - to: =(D2-C2)+0.00001 ...but I would rather not do this for obvious reasons. Thank you
(In reply to BillDuckMan from comment #13) Based on the comment I presume you didn't mean to assing this bug report to yourself.
repro 7.3+
(In reply to BillDuckMan from comment #13) > Created attachment 176154 [details] > Exported from Google Calendar - Rounding Issue > > The attached shows a rounding issue in column E, which is the difference > between columns D and C (dates/times). > > For example, opening the file in Calc I see 00:59 instead of 01:00 and 01:44 > instead of 01:45 That is fixed by actually applying the *duration* format [HH]:MM to column E.