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

# office-formula message

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

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

• From: Doug Mahugh <Doug.Mahugh@microsoft.com>
• To: "dwheeler@dwheeler.com" <dwheeler@dwheeler.com>
• Date: Wed, 2 Jul 2008 08:46:16 -0700

```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.

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

```

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