Summary: | EDITING YEARFRAC function returns incorrect results for some dates | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | Christopher M. Penalver <christopher.m.penalver> |
Component: | Spreadsheet | Assignee: | Winfried Donkers <osc> |
Status: | RESOLVED FIXED | QA Contact: | |
Severity: | enhancement | ||
Priority: | medium | CC: | erack, jeffdchang, lionel, osc, yfjiang |
Version: | 3.3.3 release | ||
Hardware: | x86 (IA32) | ||
OS: | All | ||
See Also: | https://launchpad.net/bugs/127505 | ||
Whiteboard: | target:4.1.0 target:4.0.3 target:4.2.0 | ||
i915 platform: | i915 features: |
Description
Christopher M. Penalver
2011-08-15 03:25:01 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. 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. [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 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 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 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. 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 :-) 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. See also test cases at http://plugfest.opendocsociety.org/doku.php?id=scenarios:20100415:yearfrac Note: hit Shift+Ctrl+F9 to recalculate the document. 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. 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? (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/ 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. 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. (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 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. @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 (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. 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. 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. |
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.