Bug 40100 - EDITING YEARFRAC function returns incorrect results for some dates
Summary: EDITING YEARFRAC function returns incorrect results for some dates
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.3 release
Hardware: x86 (IA32) All
: medium enhancement
Assignee: Winfried Donkers
URL:
Whiteboard: target:4.1.0 target:4.0.3 target:4.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2011-08-15 03:25 UTC by Chris Peñalver
Modified: 2013-09-18 11:09 UTC (History)
5 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 Chris Peñalver 2011-08-15 03:25:01 UTC
Downstream bug may be found at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/127505

OOo bug may be found at:
http://qa.openoffice.org/issues/show_bug.cgi?id=74069

1) lsb_release -rd
Description: Ubuntu 11.04
Release: 11.04

2) apt-cache policy libreoffice-calc
libreoffice-calc:
  Installed: 1:3.3.3-1ubuntu2
  Candidate: 1:3.3.3-1ubuntu2
  Version table:
 *** 1:3.3.3-1ubuntu2 0
        100 /var/lib/dpkg/status
     1:3.3.2-1ubuntu5 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main i386 Packages
     1:3.3.2-1ubuntu4 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/main i386 Packages

3) What is expected to happen is when one types in a cell either:

=YEARFRAC(DATE(1958,3,1),DATE(1996,1,1),1)
=YEARFRAC(DATE(1958,3,1),DATE(1995,12,31),1)

one gets two different results.

4) What happens instead is they are identical:

37.8356164384
37.8356164384
Comment 1 Jeffrey 2011-08-16 07:47:20 UTC
Confirmed for LibreOffice 3.4  340m1(Build:103) on OpenSuse Linux.

Tried it with =YEARFRAC(DATE(1958,3,1),DATE(1994,12,31),1) and =YEARFRAC(DATE(1958,3,1),DATE(1995,1,1),1), which give different results, so it is not because the difference of 1 day is so small the decimal is unchanged. Also tried with =YEARFRAC(DATE(1958,1,1),DATE(1997,12,31),1), and it gives a non-zero value, so Calc recognizes the difference of a day between 12/31 and 1/1.

Do not know why bug appears for those two values.
Comment 2 Markus Mohrhard 2011-08-18 23:11:13 UTC
mark as enhancement
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_YEARFRAC_function mentions: Excel and Calc both produce slightly erroneous results in some circumstances.
Comment 3 Björn Michaelsen 2011-12-23 12:35:40 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 4 Chris Peñalver 2012-01-17 13:01:28 UTC
Reproducible in:
lsb_release -rd
Description: Ubuntu precise (development branch)
Release: 12.04

apt-cache policy libreoffice-impress
libreoffice-impress:
  Installed: 1:3.5.0~beta2-2ubuntu3
  Candidate: 1:3.5.0~beta2-2ubuntu3
  Version table:
 *** 1:3.5.0~beta2-2ubuntu3 0
        500 http://us.archive.ubuntu.com/ubuntu/ precise/main i386 Packages
        100 /var/lib/dpkg/status
Comment 5 Rajesh Sharma 2012-04-02 08:07:07 UTC
Libra Office has take 12/31/1899 as 1 and not 1900 as base for caluclation
if we will format 01/01/1900 as number it will give you 2. There is no 0 base value. which is actully problem for all date based calculations. a base date is required for calulation. 12/30/1899 is used as 0. So first resolve the base vlaue proble then only date based calculation can be resolved
Comment 6 Winfried Donkers 2013-04-10 15:33:18 UTC
I have found the cause of the problem. It has to do with leap years.
I now have an ugly fix, which I will try to make neat and tidy before committing.
Comment 7 Winfried Donkers 2013-04-10 15:40:38 UTC
BTW there will always be the chance of a small difference in the result of  YEARFRAC(), because of leap years in case of Mode 1 (exact).
The number of days in a year on which the result is based is the year of the first date.
The result is the number of days between the two dates divided by the number of days in a year. 
So, even the number of days between two dates is identical, depending on whether the first date is a leap year, the result will differ a little bit.

This cannot be avoided, as a year has no fixed length with Mode 1 (exact).

This unavoidable difference is not what causes this bug, this bug can be fixed :-)
Comment 8 Winfried Donkers 2013-04-16 05:29:20 UTC
It was brought to my attention that YEARFRAC does not yet comply fully with http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#YEARFRAC and I intend to fix that too.
Comment 9 Eike Rathke 2013-04-17 13:56:24 UTC
See also test cases at http://plugfest.opendocsociety.org/doku.php?id=scenarios:20100415:yearfrac
Note: hit Shift+Ctrl+F9 to recalculate the document.
Comment 10 Commit Notification 2013-04-17 14:18:04 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

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

fdo#40100 make function YEARFRAC comply with ODFF Version1.2



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 11 Chris Peñalver 2013-04-21 01:44:53 UTC
Winfried Donkers, thank you for submitting a patch for this issue. The issue noted in the Description is still reproducible in:
Version 4.0.3.1 (Build ID: a67943cd4d125208f4ea7fa29439551825cfb39)
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002

Does this build not include your commit, or does it not fix the issue?
Comment 12 Winfried Donkers 2013-04-22 05:56:14 UTC
(In reply to comment #11)
Hi Christopher,

It is correct that version 4.0.3 does not have the patch. The patch will be in distributed builds starting with version 4.1. (See 'whiteboard' filed on top of this bug)
This bug has been marked as an enhancement and that means that it will be in the first release of a new minor version.
The releases 4.0.x are bugfix releases and only contain bugfixes, no enhancements.
See also https://wiki.documentfoundation.org/ReleasePlan for more information on the release plan.

I quite understand your wish to have this path as soon as possible. I will request that this patch be included in release 4.0.4 on the grounds that it is not really an enhancement and more of a bug fix.

As I am confident this bug has been fixed, I set the status back to solved.
Feel free to reopen it if you still can reproduce the bug with a build containing the patch. (Daily builds are available and do contain the patch:
http://dev-builds.libreoffice.org/daily/master/
Comment 13 Commit Notification 2013-04-22 14:23:16 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bd9238f3fe4bd004cb77e89813eb730e1e865667&h=libreoffice-4-0

fdo#40100 make function YEARFRAC comply with ODFF Version1.2


It will be available in LibreOffice 4.0.4.

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 14 Commit Notification 2013-04-24 08:26:20 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-4-0-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=73623e57b50c461ab3df95065d56908825144009&h=libreoffice-4-0-3

fdo#40100 make function YEARFRAC comply with ODFF Version1.2


It will be available already in LibreOffice 4.0.3.

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 15 Winfried Donkers 2013-05-01 06:55:51 UTC
(In reply to comment #11)

Hi Christopher,

As you may have noticed, the bug should be fixed starting with version 4.0.3RC2.

Thank you for your reminder, which resulted in putting the bug fix forward from 4.1 to 4.0.3
Comment 16 Commit Notification 2013-09-17 17:36:07 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

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

fdo#40100 make function YEARFRAC comply with ODF Version 1.2



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 17 Eike Rathke 2013-09-17 17:48:22 UTC
@Lionel:
Out of curiosity, with that change, does it still pass the tests of http://plugfest.opendocsociety.org/doku.php?id=scenarios:20100415:yearfrac
(note that Shift+Ctrl+F9 may be needed to force a recalc)

And yes, Excel 2013 for =YEARFRAC(DATE(2023,1,1),DATE(2024,1,1),1) delivers 1
Comment 18 Lionel Elie Mamane 2013-09-17 17:53:17 UTC
(In reply to comment #17)
> @Lionel:
> Out of curiosity, with that change, does it still pass the tests of
> http://plugfest.opendocsociety.org/doku.php?id=scenarios:20100415:yearfrac

Yes.
Comment 19 Commit Notification 2013-09-18 10:58:22 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e612d5cfaf1ea95d5475978fe1393732fd46ff04&h=libreoffice-4-0

fdo#40100 make function YEARFRAC comply with ODF Version 1.2


It will be available in LibreOffice 4.0.6.

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 20 Commit Notification 2013-09-18 11:09:16 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0f002a14ecdc445deb2bc8d4cc6fbe4b724ac7f6&h=libreoffice-4-1

fdo#40100 make function YEARFRAC comply with ODF Version 1.2


It will be available in LibreOffice 4.1.3.

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.