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?

Patrick Durusau <patrick@durusau.net> wrote on 04/17/2008 10:34:00 AM:

> Well, except that the definitions by the "authorities" are at least as
> vague and/or ambiguous as those of #2. If such "authorities" did have
> complete definitions it would be the "logical and defensible" approach.
> Unfortunately, I don't think those have ever been defined.

I think you are being needlessly pessimistic.  Just because Microsoft hasn't managed to define these conventions doesn't necessarily mean that others have not succeeded.  

If you look around, you will find fuller, authoritative definitions for some of these conventions.  

For example:  http://www.isda.org/c_and_a/pdf/ICMA-Rule-251.pdf

Real money is on the line.  Being one day off on a date calculation may be only 0.3% in a year, but when dealing with a 100 million dollar transaction that is real money.  

The problem we have is there are more defined conventions in existence than the 5 bases in Excel.  There appear to be at least 10 different defined conventions in use. You can see them enumerated here:


There is no easy way to tell whether what Excel does is:

1) Exactly the same as one of these conventions
2) Based on an earlier version of the these conventions
3) A buggy implementation of one of these conventions
4) A 100% correct and current implementation of some other conventions that are close but not identical to any of these conventions.

So putting together a set of well-defined date counting conventions based on external authorities -- this is easy.  But the task of doing that and being compatible with Excel -- this is not so easy.

Maybe the solution is to have more than 5 bases?  We let 0-4 be the "Excel-compatible" options that match, as much as we can ascertain, what Excel 2007 does.  Then we have additional options 5, 6, 7, etc., that match current external financial authorities exactly.  


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