Excel 2007 != Ecma spec YEARFRAC. Not even slightly. Whatshould we do?

["David A. Wheeler" <dwheeler@dwheeler.com>]

In the process of re-creating the Excel 2007 algorithms for YEARFRAC,
and comparing them to the draft ISO spec for Microsoft XML,
I've found that they are simply nothing like each other.
I can already confirm this for basis==0 and basis==1.

Three possibilities:
1. The Microsoft XML spec is drastically wrong
2. Excel 2007 is drastically wrong
3. Both are wrong.

I intend to work with Patrick to document the ACTUAL algorithms
used by Excel 2007's YEARFRAC, and along with examples of how those algorithms
produce DIFFERENT results from the algorithms defined in the Microsoft XML spec.
That will at least give us an understanding of what the differences are.

We will then need to make a strategic decision regarding spreadsheet functions.

We may need to consider dropping the basis-using functions as part of the spreadsheet spec, and later release an update to add them once we can have firm definitions of them, unless other agreements/decisions can be made soon.  We've already waited for a very long time.  ISO doesn't appear to mind ratifying specifications that are loaded with known errors.  But I don't think anyone here wants to release an OpenDocument spec that is known-wrong.

--- David A. Wheeler

========================

P.S., here's a basis==0 example.
Ecma says, for basis=0, "If the second date has a day value of 31, it is
changed to 30 days as long as the first date was not 28 or 29 February, in
which case it does not change."  But in Excel 2007,
given 2000-01-01 and 2000-01-31 with  basis 0, it returns "0.083333333",
which is 30/360.  In other words, Jan 31 does NOT change to Jan 30
(which is really weird for a "30-day month").
Details are at:
 http://www.dwheeler.com/yearfrac