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


Help: OASIS Mailing Lists Help | MarkMail Help

office message

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

Subject: Re: [office] Excel 2007 != Ecma spec YEARFRAC. Not even slightly. Whatshould we do?

For some of these bases, the behavior was intended to follow some external authority, right?  One basis was using an NASD definition, for example.  Another was for the European bourses, etc.  

So I see three choices:

1) One would be to use the external authorities, and consult their definitions and define our functions to match theirs.  So OpenFormula would be correct and current to those authorities.  This approach is logical and defensible.  If I were running a publicly traded company subject to Sarbannes-Oxley, I'd certainly want my financial functions to give the correct answer.

2) Another approach would be to match what OOXML does, which may be ambiguous, may not match the relevant external authority's definitions, and may not even be what Excel does.  I think we've found that this approach not to be workable.

3) Another approach would be to figure out what Excel actual does.  My assumption is that Excel started from an external authority, circa 1985, and what we have today is that, with possibly some implementation bugs layered on top.  The external authorities may have also changed their definitions since 1985 as well.  

However we do this, I'd recommend an exhaustive, brute force test.   Assuming we are willing to ignore the 1900 leap year weirdness in Excel, we should be able to take any range of 4 years + 1 day and calculate all possible date intervals.  So (365+365+365+366+1)^2 = 2,137,444 test cases by brute force.  If you do this in a grid, you get a 23 MB XLSX file (sorry, Excel 2007 format needed to handle the 1400+ columns.)

The basis value (currently 0) is set in cell A1.  If you can make your Python script output a similar grid, in CSV format, you could load it into a second sheet in the workbook and then have a third sheet calculate the delta.

I think this is similar to what you did before, but there may be some advantage to doing an exhaustive enumeration of test cases.  Let me know if you want that sheet.  It is too big to post to the list, but I could ftp it to by web site for download.

And for those reading this who have no idea why we have this mess, I'd recommend the Wikipedia page on Date Count Conventions as a start:  http://en.wikipedia.org/wiki/Day_count_convention

You'll see that there are many more conventions and variations on conventions than the 4 that Excel defines.  What is not certain is whether Excel's calculations map 100% into any recognized definition.


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

04/16/2008 06:34 PM
Please respond to

[office] Excel 2007 != Ecma spec YEARFRAC.  Not even slightly.  What should we do?

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:

To unsubscribe from this mail list, you must leave the OASIS TC that
generates this mail.  You may a link to this group and all your TCs in OASIS

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