[ODFF] Error in Basis, Procedure E, algorithm as used in YEARFRAC(), ...

[Eike Rathke <erack@redhat.com>]

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

-- 
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GPG key 0x6A6CD5B765632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A

Attachment: signature.asc
Description: PGP signature