Re: [office-comment] [ODFF] Error in Basis, Procedure E, algorithm as used in YEARFRAC(), ...

[Patrick Durusau <patrick@durusau.net>]

Eike,

Apologies for the slow response!

There is some question as to the calculation of YEARFRAC() in Excel. MS
is checking and we should know something in the next few weeks. (The
year portion of the formula isn't displaying when calculated? I haven't
seen it personally but that was the report.)

Hope you are at the start of a great new year!

Patrick


On 12/10/18 2:50 PM, Eike Rathke wrote:
> Hi,
>
> There's an error in ODFF 4.11.7.7 Procedure E (for Basis as used in YEARFRAC(), ...)
> https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#Basis
>
> If implemented as specified there are a few values off and also
> inconsistent with what Excel calculates, for details please see
> https://bugs.documentfoundation.org/show_bug.cgi?id=69569
>
> I propose the following changes to Procedure E:
>
> Insert at the beginning:
> * truncate(date1), truncate(date2)
> * If date1 > date2, then swap the values of date1 and date2.
>
> Change:
> 8. Otherwise, if A and is-leap-year(year(date1)) then return 366
>
> to
> 8. Otherwise, if not(A) and is-leap-year(year(date1)) then return 366
>
> Combine:
> 9. Otherwise, if a February 29 occurs between date1 and date2 then return 366
> 10. Otherwise, if date2 is a February 29, then return 366
>
> into (with stating inclusive dates)
> * Otherwise, if a February 29 occurs between date1 (inclusive) and date2 (inclusive) then return 366
>
>
> The entire Procedure E then will read:
>
> 1. truncate(date1), truncate(date2)
> 2. If date1 > date2, then swap the values of date1 and date2.
> 3. Evaluate A: year(date1) != year(date2)
> 4. Evaluate B: year(date2) != year(date1)+1
> 5. Evaluate C: month(date1) < month(date2)
> 6. Evaluate D: month(date1) == month(date2)
> 7. Evaluate E: day(date1) < day(date2)
> 8. Evaluate F: (A and B) or (A and C) or (A and D and E)
> 9. If F is true then return the average of the number of days in each year between date1 and date2, inclusive.
> 10. Otherwise, if not(A) and is-leap-year(year(date1)) then return 366
> 11. Otherwise, if a February 29 occurs between date1 (inclusive) and date2 (inclusive) then return 366
> 12. Otherwise return 365
>
>
> With many thanks to Winfried Donkers, who refined and tested this and also came
> up with a new test case document attached to the bug mentioned above, available
> as http://bugs.documentfoundation.org/attachment.cgi?id=147391
>
>   Eike
>
-- 
Patrick Durusau
patrick@durusau.net
Technical Advisory Board, OASIS (TAB)
Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300
Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)

Another Word For It (blog): http://tm.durusau.net
Homepage: http://www.durusau.net
Twitter: patrickDurusau

Attachment: signature.asc
Description: OpenPGP digital signature