Bug 133919 - FILEOPEN XLS Time values rounded incorrectly
Summary: FILEOPEN XLS Time values rounded incorrectly
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: bibisected, bisected, filter:xls, regression
: 143132 (view as bug list)
Depends on:
Blocks: XLS
  Show dependency treegraph
Reported: 2020-06-11 23:42 UTC by Steve Garry
Modified: 2021-11-19 11:40 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:

imported data transfer file (16.50 KB, application/vnd.ms-excel)
2020-06-11 23:48 UTC, Steve Garry
screenshot of opened data file (152.64 KB, image/png)
2020-06-12 00:05 UTC, Steve Garry
Comparison MSO 2010 and LibreOffice 7.1 master (363.51 KB, image/png)
2020-07-27 11:20 UTC, Xisco Faulí
Sample with single time cell in XLSX (8.12 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-12-07 18:10 UTC, Aron Budea
Exported from Google Calendar - Rounding Issue (34.83 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-11-08 14:04 UTC, BillDuckMan

Note You need to log in before you can comment on or make changes to this bug.
Description Steve Garry 2020-06-11 23:42:36 UTC
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: (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
Comment 1 Steve Garry 2020-06-11 23:48:15 UTC
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.
Comment 2 Steve Garry 2020-06-12 00:05:30 UTC
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.
Comment 3 raal 2020-06-21 09:52:11 UTC Comment hidden (obsolete)
Comment 4 raal 2020-06-21 09:57:11 UTC Comment hidden (obsolete)
Comment 5 Xisco Faulí 2020-07-27 11:20:04 UTC
Created attachment 163611 [details]
Comparison MSO 2010 and LibreOffice 7.1 master
Comment 6 Buovjaga 2020-12-07 17:29:03 UTC
(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
Comment 7 Aron Budea 2020-12-07 17:50:05 UTC
It's as expected in, but not in => 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.

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
Comment 8 Aron Budea 2020-12-07 18:10:38 UTC
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.
Comment 9 Aron Budea 2020-12-08 04:42:29 UTC
The minimal value for 17:00
- in Excel, and in Calc < 6.2: 0.708327546296297
- in Calc 6.2+: 0.708333333333328
Comment 10 b. 2021-05-24 20:15:27 UTC
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',
Comment 11 Eike Rathke 2021-05-25 11:08:56 UTC
(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.
Comment 12 Eike Rathke 2021-06-30 13:12:31 UTC
*** Bug 143132 has been marked as a duplicate of this bug. ***
Comment 13 BillDuckMan 2021-11-08 14:04:17 UTC
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
Comment 14 Aron Budea 2021-11-08 14:16:54 UTC Comment hidden (off-topic)
Comment 15 Justin L 2021-11-19 11:40:06 UTC
repro 7.3+