Re: [office-formula] Re: Excel's YEARFRAC algorithm

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

Doug Mahugh:
> > I presume that Microsoft does NOT plan on changing this algorithm?
> 
> We're not planning to change it currently.  As a general observation, we put a fairly high priority on backward compatibility in these situations, so I'd guess that we'd be more likely to do a new separate function than make any change to the behavior of the existing YEARFRAC.  But that's just my own speculation, so I'll look into this and see if I can get anything more specific.  (I was traveling Friday and haven't had a chance to discuss this with anyone else yet.)

That's what I would expect, and I think it's a good idea.  What's odd - and sent me on this spree - was that the OOXML drafts I did NOT have the same algorithm as Excel (!).

> How about if I write a loop to run my Ruby code against your test cases, to verify that our algorithms are functionally equivalent in those instances?  Were those results generated by your Python code?  Or by Excel?

Excel 2007.  So if your Ruby or VB code produces the same values as the test cases,
then it's producing the same values as Excel 2007 and the Python code.

> In any event, I could write a loop to run my code against all of the test cases in yearfrac_data_basis_all.zip and flag anywhere we differ more than a final-digit rounding error.

Okay.  I suggest an epsilon of 1E-6; that's enough to distinguish between
1/365 and 1/((365+366)/2)

--- David A. Wheeler