OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

office-comment message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

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

• From: Patrick Durusau <patrick@durusau.net>
• To: office-comment@lists.oasis-open.org
• Date: Thu, 3 Jan 2019 14:04:14 -0500

```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

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]