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

Patrick Durusau <> 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:

http://www.eclipsesoftware.biz/DayCountConventions.html

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.  

-Rob