[Upstream] [hardy] YEARFRAC function returns incorrect results for some dates

[jimgknga]

Binary package hint: openoffice.org

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

WORKAROUND: Use Gnumeric as the results are:

37.8364338364338
37.8357230348008

apt-cache policy gnumeric
gnumeric:
  Installed: 1.10.13-1ubuntu1
  Candidate: 1.10.13-1ubuntu1
  Version table:
 *** 1.10.13-1ubuntu1 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/universe i386 Packages
        100 /var/lib/dpkg/status

Original Reporter Comments: This occurs whenever the "End date" is in a leap year (New Year's Eve/Day is convenient) and the "Start date" is not in a leap year but is later than 28-Feb of its year. For example, "Start dates" between "1.1.1958" and "2.28.1958" in the above examples work correctly.

[Chris Cheney (ccheney)]

Confirmed on upstream's openoffice.org 2.4.0~rc2

[Robert Roth (evfool)]

Can anyone specify what the expected results are for the examples specified in the bug description? Thanks.

[jimgknga (jimgknga)]

Hi Robert,

I'm the guy who opened this bug report. The easiest thing to do is get a
copy of Microsoft's Excel program & use their YEARFRAC function in an
array
of cells with various values, then compare them to openoffice's look-
alike.

The expected results are also implied in the bug report description:
e.g.,
"one day apart"; meaning it is really easy to catch failing values --
just
compute the year by your own calculation, an almanac, or any number of
online tools that return things like "How many days from...".

If you don't want to trust EXCEL then consider that even if EXCEL is
occasionally wrong sometimes the question to consider is this. Is the
goal
for openoffice's version to be CORRECT or just CONSISTENT with EXCEL?
That's
a much trickier question than you might think. Years ago I worked at
IBM on
Instruction Set microcoding in System/370/390 machines & when we
fixed many
of the elementary math library functions (sine, cosine, square root,
etc..)
to be CORRECT then all kinds of things broke for users whose
calculations
manually corrected for inconsistencies in the old routines out there
that
used and depended on these (lots and lots!). Nastily they also broke for
subtle things like the fact that now "new and different" results were
returned because of the difference in roundings between the new (and
correct) and the old (wrong, sometimes, extremely wrong) function
calls and
instructions. E.g. something as simple as x=sqrt(x*x) would produce
an actual inequality if you round both sqrt(x) and Y*Y differently in
one
library versus the other. Lastly there was the fun of old but fixed
routines
having to coexist with old not-fixed yet routines! It was a
nightmare. Lots
of fun. The brilliant mathematicians at IBM Research who devised most
of the
algorithms -- some luminaries there as well! Jim Cooley and Bryant
Tuckerman
-- were both chagrined and humbled by the results of "interfacing
with the
world of users" -- not the ivory tower. :-)

The reason I put in the weird-sounding hints re: leap years is that --
having worked on date and time functions for the Ingres database
(years and
years ago :-)) and fixed issues with certain dates and with certain
times,
esp. between different time zones & the Gregorian, Julian, Russian time
jumps -- I know that leap years cause head-aches as special
conditions: skip leap
years across century changes except for millennium changes and so on ...
So, the programming algorithms, shared functions, and
special-condition handling of anyone's particular date & time
implementation
all have a lot to do with how accurately these work across the large
(but
practically bounded) input arguments spectrum. You can do an extremely
thorough job of testing these, though, as you can, e.g., blast
monotonically
increasing dates and times at it & actually exhaust the valid data
input &
combinations spectrums. Dates way in the future can be excluded as
well as
before the big bang which bounds the spectrums. Computers sure are fast.

Good luck,

-- Jim

On Dec 14, 2010, at 11:32 PM, Robert Roth wrote:

> Can anyone specify what the expected results are for the examples
> specif...

[penalvch (penalvch)]

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

[Jeffdchang (jeffdchang)]

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.

[Markus Mohrhard (moggi)]

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.

[Björn Michaelsen (bjoern-michaelsen)]

[This is an automated message.]
There are no new official OpenOffice.org releases in Ubuntu packaging anymore => Won't Fix

If the problem persists, please mark this bug as "also affects project Libreoffice" or "also affects distribution Libreoffice (Ubuntu)" if that has not happened already.

Please leave references to upstream OpenOffice.org bugs in place to allow cross pollination.

[Björn Michaelsen (bjoern-michaelsen)]

[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

[penalvch (penalvch)]

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

[Rksharma74 (rksharma74)]

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

[E-osc (e-osc)]

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.

[E-osc (e-osc)]

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 :-)

[E-osc (e-osc)]

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.

[Eike Rathke (erack)]

See also test cases at http://plugfest.opendocsociety.org/doku.php?id=scenarios:20100415:yearfrac
Note: hit Shift+Ctrl+F9 to recalculate the document.

[Libreoffice-commits (libreoffice-commits)]

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.

[penalvch (penalvch)]

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?

[E-osc (e-osc)]

(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/

[Libreoffice-commits (libreoffice-commits)]

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.

[Libreoffice-commits (libreoffice-commits)]

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.

[E-osc (e-osc)]

(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

[Libreoffice-commits (libreoffice-commits)]

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.

[Eike Rathke (erack)]

@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

[Lionel Elie Mamane (lionel-mamane)]

(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.

[Libreoffice-commits (libreoffice-commits)]

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.

[Libreoffice-commits (libreoffice-commits)]

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.

[Chris Halse Rogers (raof)]

Hello jimgknga, or anyone else affected,

Accepted libreoffice into raring-proposed. The package will build now and be available at http://launchpad.net/ubuntu/+source/libreoffice/1:4.0.4-0ubuntu1 in a few hours, and then in the -proposed repository.

Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please add a comment to this bug, mentioning the version of the package you tested, and change the tag from verification-needed to verification-done. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

[Björn Michaelsen (bjoern-michaelsen)]

As per upstream: release in versions above 4.1.0 and 4.0.3.

[Björn Michaelsen (bjoern-michaelsen)]

see https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1051955/comments/19