Bug 123212 - Wrong calculation of minutes after absolute date difference - Delta time between Start time to End time
Summary: Wrong calculation of minutes after absolute date difference - Delta time betw...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86 (IA32) All
: medium normal
Assignee: Not Assigned
Keywords: bibisectRequest, regression
Depends on:
Reported: 2019-02-06 17:38 UTC by Enzo Polo
Modified: 2019-02-07 15:55 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Date difference error (3.94 KB, image/png)
2019-02-06 17:41 UTC, Enzo Polo
date time diff (9.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-02-06 18:26 UTC, Oliver Brinzing

Note You need to log in before you can comment on or make changes to this bug.
Description Enzo Polo 2019-02-06 17:38:09 UTC
When I make the =MINUTES(AH2-AG2) of two dates with 1 hour of difference, MINUTES return 59 minutes and not 60 minutes. The number of difference is: 0.041666666664241  maybe it is due to some ROUND or FLOOR effect.

Steps to Reproduce:
1.  Create a date A
2.  Create a date B with one hour more
3.  Calculate the difference of dates
4.  Try to get the minutes of this difference

Actual Results:
59 minutes

Expected Results:
60 minutes

Reproducible: Always

User Profile Reset: No

Additional Info:
Comment 1 Enzo Polo 2019-02-06 17:41:00 UTC
Created attachment 148964 [details]
Date difference error
Comment 2 Oliver Brinzing 2019-02-06 18:26:16 UTC
Created attachment 148966 [details]
date time diff

reproducible with 

Version: (x64)
Build-ID: 90f8dcf33c87b3705e78202e3df5142b201bd805
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: 

Version: (x64)
Build ID: ad972aad6ba94c5a8dd0fd1693efdd97a184e9e9
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

but *not* with

Version: (x64)
Build-ID: dc89aa7a9eabfd848af146d5086077aeed2ae4a5
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc:
Comment 3 Oliver Brinzing 2019-02-06 18:44:02 UTC
> Expected Results:
> 60 minutes

result with LO (and e.g. AOO 4.1.5) is 0:

Comment 4 Winfried Donkers 2019-02-07 15:55:32 UTC
The standard used (Open Document Format, Part 2 OpenFormula, version 1.2) states that the value for MINUTE is not rounded:
That means that the value for MINUTE can be 0..59, but never 60. The result for HOUR isn't rounded either, whereas the result for SECOND is rounded.

The help text as referred to in comment #3 says the same.

The behaviour as stated in the standard and help text are correct for time values, but can be unexpected for duration values. 
Calc cannot differentiate between time and duration. Both are floating point values and the presentation of the value can be set by the user. There are some inconsistencies in time presentation, and http://bugs.documentfoundation.org/attachment.cgi?id=147433 demonstrates them.
And although it would be possible to differentiate between time and duration in cell formatting, this is not possible with the functions HOUR, MINUTE, SECOND. These functions are intended to show (date)time parts, not duration. Otherwise MINUTE would return 120 when the duration was 2 hours.

If you want rounded values for duration, you will have to enter (B1-A1)*24*60 (cells in attachment #148966 [details]) and use cell format to set the number of decimals you want to see.
The duration between A1 and B1 is not exactly 1 hour because of decimal to binary conversion inaccuracy. This cannot be avoided as a decimal floating point value can not always be represented _exactly_ in binary format.

I quite understand the seemingly incorrect answer as a duration is meant and not a time value. Nonetheless I mark the bug report as NOTABUG.