Bug 105548 - DATEDIF function rounding leads to a compatibility issue between calc and excel.
Summary: DATEDIF function rounding leads to a compatibility issue between calc and excel.
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6 all versions
Hardware: All All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:5.4.0 target:5.3.1
Keywords:
Depends on:
Blocks:
 
Reported: 2017-01-26 15:55 UTC by Christopher POTTER
Modified: 2017-03-14 11:29 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Christopher POTTER 2017-01-26 15:55:31 UTC
Description:
If you use DATEDIF between 2 dates (including hours), the rounding must be a ceil rounding

LibreOffice does a floor rounding.

Steps to Reproduce:
1. A1="01/01/2017 08:00:00"
2. A2="03/01/2017 07:00:00"
3. B2=DATEDIF(A2;A3;"d")


Actual Results:  
1

Expected Results:
2


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36 OPR/42.0.0.239320787 (Edition EXP62)
Comment 1 m_a_riosv 2017-01-27 01:09:40 UTC
From the help, https://help.libreoffice.org/Calc/DATEDIF
"
DATEDIF
This function returns the number of whole days, months or years between Start 
                                    ***********
date and End date.
"
even ODF 1.2 it's not explicit about that.
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018176_715980110

If hours are not given then result it's as you expect.
With
=DATEDIF(INT(A1);INT(A2);"d")
the decimal part (time) of A1 and A2 it's eliminated

Sounds reasonable to me how it works, providing hours then there are taked for the calculation.
Comment 2 Christopher POTTER 2017-01-27 06:38:49 UTC
Forgot to specify that Excel and WPS gives the right result.

This leads to *critical* compatibility problem between LO and Excel macros
Comment 3 m_a_riosv 2017-01-27 09:51:47 UTC
I guess you mean functions not macros.
Comment 4 Mike Kaganski 2017-01-27 10:30:31 UTC
I confirm that this is a bug.

The wording is quite clear: the difference between *dates*. This doesn't tell about difference between moments in time.

We have a data type that combines both date *and* time into a single floating-point number. But that doesn't mean that this makes term "date" to magically equal to term "datetime". If we use, e.g., function =DATEVALUE("01/01/2017 08:00:00"), then it gives 42736 (without fractional part).

So, the DATEDIF must be equal to DATEVALUE(date1)-DATEVALUE(date2).
Comment 5 m_a_riosv 2017-01-27 16:35:10 UTC
(1) https://gerrit.libreoffice.org/gitweb?p=core.git;a=commitdiff;h=4e71be498903dee5bf719a73f4976bb5356335dd
(2) https://gerrit.libreoffice.org/gitweb?p=core.git;a=commitdiff;h=40c8170c5281cd7b19313a79f902ef9715351c9f

On both commits dates variables are defined as:
 double nDate2    = GetDouble();
 double nDate1    = GetDouble();

and days are calculate
(1) int  dd = nDate2 - nDate1; 
(2) long dd = nDate2 - nDate1;
Comment 6 Winfried Donkers 2017-01-29 11:24:20 UTC
Calc normally converts floating point values (like datetime) to integer values (like date) by 'floor rounding'.
In this case, as indicated in comment #5, it doesn't.

I'll fix that.
Comment 7 Commit Notification 2017-01-30 14:56:19 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e7606f1f19b2970f0160075f56d4d97029f1e47a

tdf#105548 fix incorrect DATEDIF result.

It will be available in 5.4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2017-01-30 18:15:44 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4b605ca21b15ff4cbd734291e21ab02c5e57c424&h=libreoffice-5-3

tdf#105548 fix incorrect DATEDIF result.

It will be available in 5.3.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 m_a_riosv 2017-02-01 00:29:34 UTC
Tested
Version: 5.4.0.0.alpha0+
Build ID: 2670ca3fc597decae78499d1397539668eb84e5e
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2017-01-31_05:32:46
Locale: es-ES (es_ES); Calc: CL

Thanks Winfried.
Comment 10 Mike Kaganski 2017-02-02 05:45:06 UTC
Thank you Winfried!

Zdeněk, is this worth to be added to a test sheet?
Comment 11 Mike Kaganski 2017-02-02 05:46:48 UTC
Oh sorry!
I didn't look at the commit itself - it is already done! Sorry - and thanks again Winfried!
Comment 12 morvan 2017-03-14 11:29:53 UTC
Hi. I fought behavior was the right. I put, purpose of testing, in B1, "03/01/2017 07:59:56" and it goes on with "1" result until 48 hours achieved; I reiterate, I think it is what I fought was the correct. But, it was fixed and it is what counts (no pun of words). Congrats for all involved in solving (and in pointing it too).