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

# office message

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

Subject: Re: [office] YEARFRAC, basis==0

• From: "David A. Wheeler" <dwheeler@dwheeler.com>
• To: office@lists.oasis-open.org
• Date: Mon, 28 Apr 2008 11:17:52 -0400 (EDT)

```Eike Rathke:
> Could you please check if your findings now produce identical results
> with the data attached to
> http://www.openoffice.org/issues/show_bug.cgi?id=84934
>
> If so, this would mean that the algorithms for DAYS360() US-method and
> YEARFRAC(...;basis=0) would be identical. If not, it would raise the
> question why not ...

I've been focusing on YEARFRAC, not DAYS360, because _lots_ of functions
use the "Basis" parameter that YEARFRAC implements.

DAYS360 takes an optional 3rd parameter, but that parameter is a
"Method" and _NOT_ a "basis" value.  You would _THINK_ that the "Method"
parameter of DAYS360 implemented a subset of the basis algorithms.
But as far as I can tell, Method is NOT a subset of Basis in Excel.

I know of one difference already, without deeper analysis, just from
the bug report.  In Excel, in all the algorithms with basis =0 through 4
DAYS360(A,B)=-DAYS360(B,A).  That is, if date1 > date2,
then it just flips the date positions: date1, date2 = date2, date1.
But for DAYS360(), if date1 > date2, then according to the OOo
bug report, Excel extrapolates backwards - instead of flipping the dates -
resulting in up to 2 days' difference from the "flip" approach.

There may be other differences too, but clearly they are NOT the
same algorithm, at least according to the bug report information
I have.

So we will have to do the same analysis with
DAYS360 as we did with YEARFRAC.  The good news is that we now have
an infrastructure to do the analysis.  If someone's willing to create some
test case results, I can complete the analysis in less than a day.

I think we need to carefully document what Excel 2007 _ACTUALLY DOES_,
cross-compare to other stuff, and then create reasonable mappings of
basis (and method) values to those algorithms.  That's unfortunate, but
I don't see another way of getting rid of the problem.

Perhaps someone could create the massive cross-comparison for DAYS360
for Method=0 and Method=1?  Then I can use the same tools I have now
to do the analysis.

--- David A. Wheeler
```

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