[Libreoffice-bugs] [Bug 69569] Implementation of YEARFRAC function inconsistent with Excel

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=69569

--- Comment #17 from dahrion <dahrion456 at mega.zik.dj> ---
I had to implement the yearfrac function and can confirm that the error is in
the specification.
Specifically in ODFv1.2 part 2, §4.11.7.7 line 8
it shoul be not(A)
also in line 9 the between is inclusive

How did i came to this conclusion ?
the specification is clearly based on the following pdf
https://www.dwheeler.com/yearfrac/excel-ooxml-yearfrac.pdf

if you check the appears_le_year(date1, date2) you have
y1==y2 || ((y1+1)==y2 && (m1>m2 || (m1==m2 && d1>=d2)))

the odf specify the inverse of that clause:

y1==y2 || ((y1+1)==y2 && (m1>m2 || (m1==m2 && d1>=d2)))
!(y1==y2) && !((y1+1)==y2 && (m1>m2 || (m1==m2 && d1>=d2)))
!(y1==y2) && (!(y1+1)==y2 || !(m1>m2 || (m1==m2 && d1>=d2)))
!(y1==y2) && (!(y1+1)==y2 || (!m1>m2 && !(m1==m2 && d1>=d2)))
!(y1==y2) && (!(y1+1)==y2 || (!m1>m2 && (!m1==m2 || !d1>=d2)))
(y1!=y2) && ((y1+1)!=y2 || (m1<=m2 && (m1!=m2 || d1<d2)))

(m1<=m2 && (m1!=m2 || d1<d2)) = (m1<=m2 && m1!=m2)||(m1<=m2 && d1<d2) = m1<m2
|| (m1<=m2 && d1<d2) = m1<m2 || (m1=m2 && d1<d2)

(y1!=y2) && ((y1+1)!=y2 ||  m1<m2 || (m1=m2 && d1<d2))

which is line 6 in the spec: Evaluate F: (A and B) or (A and C) or (A and D and
E)

also the pdf doesnt specify the feb29_between(date1, date2) function, nor the
specification; especially its vagueness about the dates being inclusive or
exclusive.

so we can consider the specification as a representation of the algorithm in
the pdf
the pdf states that:
if (date1.year == date2.year and is_leap_year(date1.year)):
 year_length = 366.

which is equivalent to line 8 of the specs except with not(A) instead of A

The only inconsistency left is the 29 between the dates date1 and date2, we
wrongly assume it is exclusive in both date based on line 10 in the specs wich
is the equivalent of the test ||(date2.month == 2 and date2.day == 29) in the
pdf
with lot of testing it appears that the date1 is included when checking
feb29_between(date1, date2)

so far my implementation is the same as excel

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180810/17b07828/attachment-0001.html>