Note: There was a typo in the url to the test spreadsheets. The url is http://finmath.net/spreadsheets/Day%20Count%20Fractions.zip - but I attached that file anyway.

Note: I have checked the modifications made by Lionel Elie Mamane at http://cgit.freedesktop.org/libreoffice/core/commit/?id=0f002a14ecdc445deb2bc8d4cc6fbe4b724ac7f6&h=libreoffice-4-1
- just looking at the code, since I have no access to the daily build.

It appears as if this modification makes LibreOffice consistent with the documentation BUT not consistent with Excel's implementation of YEARFRAC.

To make that precise. If you take the implementation at
http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java

the difference is in the line
			if(isStartLeapYear && isEndLeapYear)
here LibreOffice uses
			if(isStartLeapYear && !isEndLeapYear)
instead.

(In reply to comment #2)
> Note: I have checked the modifications made by Lionel Elie Mamane...
> It appears as if this modification makes LibreOffice consistent with the
> documentation BUT not consistent with Excel's implementation of YEARFRAC.

It looks like we have two choices here
1) Change our code to become consistent with Excel's implementation
2) Document the difference, e.g. on https://help.libreoffice.org/Calc/YEARFRAC

Lionel - Thoughts?

(In reply to comment #3)
> (In reply to comment #2)
> > Note: I have checked the modifications made by Lionel Elie Mamane...
> > It appears as if this modification makes LibreOffice consistent with the
> > documentation BUT not consistent with Excel's implementation of YEARFRAC.
> 
> It looks like we have two choices here
> 1) Change our code to become consistent with Excel's implementation
> 2) Document the difference, e.g. on
> https://help.libreoffice.org/Calc/YEARFRAC
> 
> Lionel - Thoughts?

I will not, under my decision / responsibility, make LibreOffice Calc depart from the ODF standard. Whether the ODF standard should be "fixed" to match Excel, or if the YEARFRAC option should be a different function in .ods files and in .xls(x) files is a question I punt to the Calc experts.

I consider authoritative ODF 1.2 http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017922_715980110
as opposed to https://www.oasis-open.org/committees/document.php?document_id=39507

I came into bug 40100 as a *user* because LibreOffice was giving me wrong results for an "obvious" case like "=YEARFRAC(DATE(2023;1;1);DATE(2024;1;1);1)", so I fixed it. As long as I'm able to get sane results fom my LibreOffice in some way, I don't care much about Excel compatibility. I mean: if Excel gives results that are "wrong", then either give correct results in LibreOffice, or have "basis 5" be the "right result" in LibreOffice, so that I can do "=YEARFRAC(DATE(2023;1;1);DATE(2024;1;1);5)" and get the result I expect. But don't cripple my LibreOffice for the sake of Excel compatibility.

Maybe I would like to add that the ACT/ACT implementation of Excel AND LibreOffice is wrong - in the sense that is does not agree with the (most popular) ISDA definition of ACT/ACT.

I would vote for including new „basis“ definitions and have basis 4 be the ACT/ACT EXCEL.

Am 03.07.2014 um 17:47 schrieb bugzilla-daemon@freedesktop.org:

> Lionel Elie Mamane changed bug 69569 
> What	Removed	Added
> CC	  	kyoshida@slickedit.com, libreoffice@kohei.us, markus.mohrhard@googlemail.com            
> 
> Comment # 4 on bug 69569 from Lionel Elie Mamane
> (In reply to comment #3)
> > (In reply to comment #2)
> > > Note: I have checked the modifications made by Lionel Elie Mamane...
> > > It appears as if this modification makes LibreOffice consistent with the
> > > documentation BUT not consistent with Excel's implementation of YEARFRAC.
> > 
> > It looks like we have two choices here
> > 1) Change our code to become consistent with Excel's implementation
> > 2) Document the difference, e.g. on
> > https://help.libreoffice.org/Calc/YEARFRAC
> > 
> > Lionel - Thoughts?
> 
> I will not, under my decision / responsibility, make LibreOffice Calc depart
> from the ODF standard. Whether the ODF standard should be "fixed" to match
> Excel, or if the YEARFRAC option should be a different function in .ods files
> and in .xls(x) files is a question I punt to the Calc experts.
> 
> I consider authoritative ODF 1.2
> http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017922_715980110
> as opposed to
> https://www.oasis-open.org/committees/document.php?document_id=39507
> 
> I came into bug 40100 as a *user* because LibreOffice was giving me wrong
> results for an "obvious" case like
> "=YEARFRAC(DATE(2023;1;1);DATE(2024;1;1);1)", so I fixed it. As long as I'm
> able to get sane results fom my LibreOffice in some way, I don't care much
> about Excel compatibility. I mean: if Excel gives results that are "wrong",
> then either give correct results in LibreOffice, or have "basis 5" be the
> "right result" in LibreOffice, so that I can do
> "=YEARFRAC(DATE(2023;1;1);DATE(2024;1;1);5)" and get the result I expect. But
> don't cripple my LibreOffice for the sake of Excel compatibility.
> 
> You are receiving this mail because:
> You reported the bug.

I played a bit with Excel and LibreOffice. I found one example where they differ:

=YEARFRAC(DATE(2024;3;1);DATE(2025;3;1);1)

This, naively, should be "1". That's what Excel gives. LibreOffice gives 365/366...

(In reply to comment #6)
> I played a bit with Excel and LibreOffice. I found one example where they
> differ:
> 
> =YEARFRAC(DATE(2024;3;1);DATE(2025;3;1);1)
> 
> This, naively, should be "1". That's what Excel gives. LibreOffice gives
> 365/366...

This is because of clause 8 of ODFv1.2 part 2, §4.11.7.7
I guess I'm slipping into the "ODF has the wrong definition/algorithm" camp.

(In reply to comment #7)
>
> This is because of clause 8 of ODFv1.2 part 2, §4.11.7.7
> I guess I'm slipping into the "ODF has the wrong definition/algorithm" camp.

If would like to add that both Excel and LibreOffice have a wrong algorithm - in the following sense that both algorithms do not comply with the ACT/ACT ISDA definition which is
a) the most popular definition of ACT/ACT with relevance to industry and
b) much simpler (=n1/365 + n2/366 where n1 = number of days falling in non-leap year and n2 = number of days falling in leap year).

So "naively should be" or "defined in ODF" is IMHO not very relevant. 

How about:
- use base 4 for the Excel compatible implementation
- introduce a new base or even a new function for ACT/ACT ISDA
- if necessary, implement LibreOffices method too (as part of the new function of base 6?)

Or:
- Have the definition of base 4 configurable.

(In reply to comment #8)
> (In reply to comment #7)

>> This is because of clause 8 of ODFv1.2 part 2, §4.11.7.7
>> I guess I'm slipping into the "ODF has the wrong definition/algorithm" camp.

> How about:
> - use base 4 for the Excel compatible implementation

You mean "base 1" maybe? "base 4" is "European 30/360".

(In reply to comment #7)
> (In reply to comment #6)
> > I played a bit with Excel and LibreOffice. I found one example where they
> > differ:
> > ...
> I guess I'm slipping into the "ODF has the wrong definition/algorithm" camp.

Sounds like this bug is relevant enough for us to carry on serious discussion about our implementation and the ODF spec, hence I'm going to move it to NEW.