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

[Doug Mahugh <Doug.Mahugh@microsoft.com>]

David,

FYI, I've learned why the Method=3 case is there in the code below.  That routine (TmpDays360Nasd) is used by some other functions in Excel, such as ACCRINT, but Method=3 never occurs in YEARFRAC's use of TmpDays360Nasd.  So it's not "dead code" from our perspective, although it is indeed irrelevant to any discussion or description of YEARFRAC.

- Doug

-----Original Message-----
From: Doug Mahugh
Sent: Wednesday, July 02, 2008 8:46 AM
To: 'dwheeler@dwheeler.com'
Cc: office-formula@lists.oasis-open.org
Subject: RE: [office-formula] Re: Excel's YEARFRAC algorithm

David,

We've reviewed the Basis section's English text, and this looks good.  It matches the logic of the VBA sample, which accurately reflects the behavior of Excel.

On the Mode=3 question, I'm assuming we're talking about this code (in the VBA sample):

    If (EndMonth = 2 And FIsEndOfMonth(EndDay, EndMonth, EndYear)) And ((StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Or Method = 3) Then
        EndDay = 30
    End If
    If EndDay = 31 And (StartDay >= 30 Or Method = 3) Then
        EndDay = 30
    End If

I've confirmed that this matches Excel's code.  As to whether it's important to implement it in this particular way, I'm not certain -- are you saying that this appears to deviate from the intent of Basis=3?

Regards,
Doug


-----Original Message-----
From: David A. Wheeler [mailto:dwheeler@dwheeler.com]
Sent: Friday, June 20, 2008 1:21 PM
To: Doug Mahugh
Cc: office-formula@lists.oasis-open.org
Subject: Re: [office-formula] Re: Excel's YEARFRAC algorithm

Doug Mahugh:
> David,
>
> I've finished testing and concluded that the VBA sample I had provided does indeed match Excel's behavior in all cases.

That's excellent news.  The Ruby story is interesting, but I don't think we need
to worry about it for now.

> So we can proceed on the basis that we know what Excel is doing, and your Python sample and my VBA sample both mirror Excel's actual YEARFRAC implementation accurately.

That _should_ be true. Of course, it's possible that none of the test cases exercise
a difference, though that seems improbable.

What I did is turn the Python code into an English spec, which SHOULD be the same.  There's a risk that this dropped something important, of course.  Care to take a look at the "Basis" section's English text and confirm that the definition is correct (at least for YEARFRAC, but really, we want it correct period)?

One oddity: The Excel code has special "Mode=3" handling that is quite odd - is that just dead code, or is it important for something?

--- David A. Wheeler

---------------------------------------------------------------------
To unsubscribe from this mail list, you must leave the OASIS TC that
generates this mail.  You may a link to this group and all your TCs in OASIS
at:
https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php