[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. What should we do?
Rob, From below: > 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 Well, since you found them perhaps you should invite them to join the TC and the formula SC. They appear to have a lot of resource information and one suspects expertise in the area. Hope you are having a great day! Patrick firstname.lastname@example.org wrote: > > Patrick Durusau <email@example.com> 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 > > -- Patrick Durusau firstname.lastname@example.org Chair, V1 - US TAG to JTC 1/SC 34 Convener, JTC 1/SC 34/WG 3 (Topic Maps) Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300 Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)