OASIS Open Document Format for Office Applications (OpenDocument) TC

Expand all | Collapse all

Excel's YEARFRAC algorithm

Doug Mahugh

Doug Mahugh06-12-2008 14:02

  • 1.  Excel's YEARFRAC algorithm

    Posted 06-12-2008 14:02



  • 2.  Re: Excel's YEARFRAC algorithm

    Posted 06-13-2008 16:35
    Doug Mahugh:
    > Attached are two programs that demonstrate the algorithm Excel uses for
    > the YEARFRAC function.
    
    Excellent!  This is very useful information - thanks for taking the time to make it available to us!
    
    For comparison purposes, here is the description and Python code that I _believe_ implements the same algorithm.  By cross-comparing, we should gain much more confidence in it.  You can get more info at: http://www.dwheeler.com/yearfrac
    
    I presume that Microsoft does NOT plan on changing this algorithm?  Because if they do, any program that uses "Basis" could produce a different answer (and cause big $ changes).  I know that the draft OOXML rules are different; our current plan was to use the "current Excel rules" for basis 0-5, and permit "add 32" for those who want to implement the OOXML algorithms.
    
    --- David A. Wheeler
    
    YEARFRAC takes two dates, determines the “number of days different”, and divides that by the “number of days in the year”.  Its interpretation of these two terms depends on the “basis” value, which is defined as follows:
    
    Basis 0 or omitted: 30/360.  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference of days is 0.  Assumes that each month has 30 days and the total number of days in the year is 360 by making the following adjustments:
    If both day-of-months are 31, they are changed to 30
    Otherwise, if date1’s day-of-month is 31, it is changed to 30
    Otherwise, if date1’s day-of-month is 30 and date2’s day-of-month is 31, date2’s day-of-month is changed to 30 (note that date2’s day-of-month will stay 31 if date1’s day < 30)
    Otherwise, if both dates are the last day of February in their respective years, both day-of-month is changed to 30
    Otherwise, if date1 is the last day of February, its day-of-month is changed to 30
    
    Basis 1: Actual/actual.  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference in days is 0.  If date1 and date2 not “less than or equal to a year apart” (as defined below), then the days in the years between the dates is the average number of days in the years between date1 and date2, inclusive.  Otherwise, the days in the years between the dates is 365, except for these cases (where it is 366):  the dates are in the same year and it is a leap-year, a February 29 occurs between the two dates, or date2 is February 29.
    
    To determine if date1 and date2 are “less than or equal to a year apart” for purposes of this algorithm, one of these conditions much be true:
    The two dates have the same year
    Date2’s year is exactly one more than date1’s year, and ((date1.month > date2.month) or ((date1.month == date2.month) and (date1.day >= date2.day)))
    
    Basis 2: Actual/360. Computes the actual difference in days, and presumes there are always 360 days per year.
    
    Basis 3: Actual/365. Computes the actual difference in days, and presumes there are always 365 days per year.
    
    Basis 4: 30/360.  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference of days is 0.  Assumes that each month has 30 days and the total number of days in the year is 360; any day-of-month (in date1, date2, or both) with a value of 31 is changed to 30.  Note that February dates are never changed, because there is no February 31.
    
    To compute a date difference between two dates that use an “actual date” system (basis 1, 2, and 3), perform the swapping as discussed above (so date2 is always after date1),  consider both dates as the number of days after the same fixed date (the “epoch”), and compute date2-date1.
    
    To compute a date difference between two dates that use an “30 month day” system (basis 0 and 4), first perform modifications described above (so date2 is always after date1), and then compute the difference as (date2.year*360+date2.month*30+date2.day) - (date1.year*360 + date1.month*30 + date1.day).
    
    Here is pseudocode that implements the derived definitions for YEARFRAC (if these differ from the text above, the pseudocode is correct; please contact the author of any differences):
    
    def appears_le_year(date1, date2):
      # Returns True if date1 and date2 "appear" to be 1 year or less apart.
      # This compares the values of year, month, and day directly to each other.
      # Requires date1 <= date2; returns boolean.  Used by basis 1.
      if date1.year == date2.year:
        return True
      if (((date1.year + 1) == date2.year) and
             ((date1.month > date2.month) or
             ((date1.month == date2.month) and (date1.day >= date2.day)))):
        return True
      return False
    
    def basis0(date1,date2):
      # Swap so date1 <= date2 in all cases:
      if date1 > date2:
        date1, date2 = date2, date1
      if date1 == date2:
        return 0.0
      # Change day-of-month for purposes of calculation.
      date1day, date1month, date1year = date1.day, date1.month, date1.year
      date2day, date2month, date2year = date2.day, date2.month, date2.year
      if (date1day == 31 and date2day == 31):
        date1day = 30
        date2day = 30
      elif date1day == 31: date1day = 30
      elif (date1day == 30 and date2day == 31): date2day = 30
      # Note: If date2day==31, it STAYS 31 if date1day < 30.
      # Special fixes for February:
      elif (date1month == 2 and date2month == 2 and last_day_of_month(date1)
          and last_day_of_month(date2)):
         date1day = 30  # Set the day values to be equal
         date2day = 30
      elif date1month == 2 and last_day_of_month(date1):
         date1day = 30 # "Illegal" Feb 30 date.
      daydiff360 = ( (date2day + date2month * 30 + date2year * 360) -
                     (date1day + date1month * 30 + date1year * 360))
      return daydiff360 / 360.
    
    def basis1(date1,date2):
      # Swap so date1 <= date2 in all cases:
      if date1 > date2:
          date1, date2 = date2, date1
      if date1 == date2:
          return 0.0
      if appears_le_year(date1, date2):
        if (date1.year == date2.year and is_leap_year(date1.year)):
          year_length = 366.
        elif (feb29_between(date1, date2) or
               (date2.month == 2 and date2.day == 29)): # fixed, 2008-04-18
          year_length = 366.
        else:
          year_length = 365.
        return diffdays(date1, date2) / year_length
      else:
        num_years = (date2.year - date1.year) + 1
        days_in_years = diffdays(date(date1.year, 1, 1), date(date2.year+1, 1, 1))
        average_year_length = days_in_years / num_years
        return diffdays(date1, date2) / average_year_length
    
    def basis2(date1,date2):
      # Swap so date1 <= date2 in all cases:
      if date1 > date2:
          date1, date2 = date2, date1
      return diffdays(date1, date2) / 360.
    
    def basis3(date1,date2):
      # Swap so date1 <= date2 in all cases:
      if date1 > date2:
          date1, date2 = date2, date1
      return diffdays(date1, date2) / 365.
    
    def basis4(date1,date2):
      # Swap so date1 <= date2 in all cases:
      if date1 > date2:
        date1, date2 = date2, date1
      if date1 == date2:
        return 0.0
      # Change day-of-month for purposes of calculation.
      date1day, date1month, date1year = date1.day, date1.month, date1.year
      date2day, date2month, date2year = date2.day, date2.month, date2.year
      if date1day == 31: date1day = 30
      if date2day == 31: date2day = 30
      # Remarkably, do NOT change Feb. 28 or 29 at ALL.
      daydiff360 = ( (date2day + date2month * 30 + date2year * 360) -
                     (date1day + date1month * 30 + date1year * 360))
      return daydiff360 / 360.
    
    


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

    Posted 06-16-2008 15:17
    > 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.)
    
    > For comparison purposes, here is the description and Python code that I _believe_ implements the same algorithm.  By cross-comparing, we should gain much more confidence in it.
    
    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?  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.  I'll plan to do that this week.
    
    Regards,
    Doug
    
    
    


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

    Posted 06-17-2008 02:09
    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
    


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

    Posted 06-20-2008 01:48
    David,
    
    I've finished testing and concluded that the VBA sample I had provided does indeed match Excel's behavior in all cases.  The Ruby sample I had provided has a subtle rounding error that occurs very rarely and I haven't been able to resolve.  But in the cases where the Ruby sample doesn't match Excel, the VBA sample does match Excel, so that's not a problem we need to resolve at this point.
    
    To give you a feel for how rarely the rounding error occurs, the attached program matches Excel's output on the first 150,000 test cases and then fails on line 151,482 of yearfrac_data_basis0.txt (although the VBA version does not fail) as follows:
    
     startdate: 1999-02-28
     enddate:   2001-02-28
     basis:     0
     Excel result: 2.000000
     VBA sample:   2.000000
     Ruby sample:  1.994444
    
    (Note that for these purposes I defined "fail" as a result that differs from Excel's by more than 1E-6 as you suggested.)
    
    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.  I'll try to resolve the question of what's happening in the Ruby sample, but that will just be a third implementation of the same behavior and isn't necessary for us to move forward on your YEARFRAC proposal.
    
    Regards,
    Doug
    
    
    


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

    Posted 06-20-2008 20:21
    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
    


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

    Posted 06-23-2008 01:19
    Hi David,
    
    I'm not sure what the intent of Mode=3 was, but I'll try to track down the answer and also get somebody to take a close look at how the Python comments compare to corresponding details in Excel's YEARFRAC.
    
    FYI, I'm traveling through Tuesday with limited email access.  I won't be able to make tomorrow's call, unfortunately.
    
    Regards,
    Doug
    
    


  • 8.  Microsoft ODF workshop

    Posted 06-23-2008 01:45
    Hi everyone,
    
    As you know, Microsoft has recently announced support for ODF in the next service pack for Office (SP2 for Office 2007, expected to be released in the first half of 2009).  In conjunction with that announcement, we would like to invite all members of the OASIS OpenDocument Format TC and subcommittees to a 1-day DII workshop on how Office will support ODF.  The workshop will take place on Wednesday, July 30, 2008 in Redmond, Washington.
    
    This event will be an opportunity for you to see an early demonstration of our ODF support, meet members of the Office PM/dev teams responsible for ODF support, and hear how we're looking at document format interoperability in general.  We're looking forward to the opportunity to get to know each of you and to learn about your view of these issues, and the day will include plenty of unstructured discussion time for those purposes.
    
    The Forum will be an all-day event on the Microsoft campus in Redmond, followed by a hosted dinner event at a nearby location.  We're still working out the details of the day, but it will include three general types of activities:
    
    - Presentations from members of the Office product team, to explain our approach to ODF support and demonstrate some of the specific functionality we're planning.
    - Hands-on lab time, to give you an opportunity to try out a pre-release version of our ODF  support.
    - Discussion time, so that we can hear your feedback on topics of interest to document format implementers.
    
    If you are interested in attending, please let me know by June 30th by sending email to dmahugh@microsoft.com.  We will have limited space, so I wanted to extend this invitation to the ODF TC members first so that we can accommodate as many of you as would like to attend.  We will provide additional information regarding logistics, local hotels, etc to confirmed attendees.
    
    This event will provide a unique opportunity to see firsthand how Microsoft is planning to implement ODF support in Office, and we hope that you will be able to join us.  If you have any questions or need additional information, please let me know.
    
    Best regards,
    Doug Mahugh
    
    


  • 9.  Re: [office] Microsoft ODF workshop

    Posted 06-23-2008 12:44
    Dear Doug,
    
    Le 23 juin 08 à 03:45, Doug Mahugh a écrit :
    
    > Hi everyone,
    >
    > As you know, Microsoft has recently announced support for ODF in the  
    > next service pack for Office (SP2 for Office 2007, expected to be  
    > released in the first half of 2009).  In conjunction with that  
    > announcement, we would like to invite all members of the OASIS  
    > OpenDocument Format TC and subcommittees to a 1-day DII workshop on  
    > how Office will support ODF.  The workshop will take place on  
    > Wednesday, July 30, 2008 in Redmond, Washington.
    >
    > This event will be an opportunity for you to see an early  
    > demonstration of our ODF support, meet members of the Office PM/dev  
    > teams responsible for ODF support, and hear how we're looking at  
    > document format interoperability in general.  We're looking forward  
    > to the opportunity to get to know each of you and to learn about  
    > your view of these issues, and the day will include plenty of  
    > unstructured discussion time for those purposes.
    >
    > The Forum will be an all-day event on the Microsoft campus in  
    > Redmond, followed by a hosted dinner event at a nearby location.   
    > We're still working out the details of the day, but it will include  
    > three general types of activities:
    >
    > - Presentations from members of the Office product team, to explain  
    > our approach to ODF support and demonstrate some of the specific  
    > functionality we're planning.
    > - Hands-on lab time, to give you an opportunity to try out a pre- 
    > release version of our ODF  support.
    > - Discussion time, so that we can hear your feedback on topics of  
    > interest to document format implementers.
    >
    > If you are interested in attending, please let me know by June 30th  
    > by sending email to dmahugh@microsoft.com.  We will have limited  
    > space, so I wanted to extend this invitation to the ODF TC members  
    > first so that we can accommodate as many of you as would like to  
    > attend.  We will provide additional information regarding logistics,  
    > local hotels, etc to confirmed attendees.
    >
    > This event will provide a unique opportunity to see firsthand how  
    > Microsoft is planning to implement ODF support in Office, and we  
    > hope that you will be able to join us.  If you have any questions or  
    > need additional information, please let me know.
    >
    
    Thank you for this invitation. I have to say I have some trouble  
    understanding why you would like to gather the OASIS TC in Redmond. It  
    seems to me a bit cumbersome, and a bit bizarre to invite everyone  
    here in Redmond as if nothing had ever happened before. Perhaps, as  
    some would put it, "it's a bit too early for that". Besides, I'm sure  
    we can have that event anywhere else . As you may know, the OASIS ODF  
    TC (together with its subcommittees and the OASIS ODF Adoption TC)  
    runs several panels and conferences worldwide. The next one, if I am  
    not mistaken, will take place in Beijing during the ODF Track at the  
    OpenOffice.org Conference. It is scheduled to take place around  
    October-November 2008.
    
    This ODF track is independent from the OpenOffice.org Conference as  
    several different and competing vendors attend this conference track.  
    We would really appreciate to have you and the Microsoft Office team  
    there.
    
    I hope you understand my reaction to your invitation but please rest  
    assured that I really appreciate it. That being said, I only express  
    myself on the behalf of my company, Ars Aperta, (mostly four French  
    guys in a garage) and my views may not represent the ones of the whole  
    OASIS ODF TC.
    
    Best Regards,
    
    Charles-H. Schulz
    Associé / Founding Partner,
    Ars Aperta
    
    


  • 10.  Re: [office] Microsoft ODF workshop

    Posted 06-23-2008 13:12
    On Mon, Jun 23, 2008 at 8:43 AM, Charles-H. Schulz
    


  • 11.  RE: [office] Microsoft ODF workshop

    Posted 07-07-2008 14:39
    Hi everyone, this is to follow up on the question about virtual participation in our workshop ...
    
    I’ve looked into this, and unfortunately we won’t be able to enable real-time virtual participation.  We’re looking into the feasibility of videotaping some/all of the sessions, however, to make them available after the event.  If people attending the event aren’t comfortable with that, then we’ll need to look at potentially distributing just the presentation slides, or potentially re-presenting via a webcast.  The details are still being worked out, but we'll find a way to get the content out more broadly after the workshop is completed.
    
    Regards,
    Doug
    
    


  • 12.  RE: [office] Microsoft ODF workshop

    Posted 06-23-2008 21:41
    Hi Charles,
    
    The short answer to why this event is in Redmond is "that's where our ODF developers are."  We've received many questions recently about details of our planned ODF support, and we decided it would be good to have an event where we could explain our plans, show some pre-release code, and give people a chance to talk to the dev team about how we're approaching ODF support.  The only realistic way to do that is in Redmond, since that's where the dev/PM people are located.  It would be difficult to find a time when they could all be in another location (probably impossible until the next version of Office is close to shipping), so this Redmond event is the only way we can give everyone direct access to the people actually doing our ODF implementation work.
    
    Regarding the invitation to the ODF TC, that was my idea.  I thought some might find it odd if we had an ODF-related event and didn't start by inviting the TC, and I wanted to make sure that everyone here knows they're welcome to attend if they're interested in learning more about our implementation.  We certainly weren't trying to gather the OASIS TC specifically -- I and others at Microsoft will be reaching out to others who have expressed an interest as well, but we started here.
    
    Regarding Bruce's question on allowing virtual participation, I'm looking into some options for that and will share info when I have it.
    
    Regards,
    Doug
    
    
    


  • 13.  Re: [office] Microsoft ODF workshop

    Posted 06-23-2008 21:44
    
    
    
    
    .. and this was appreciated.


    On 23/06/08 2:41 PM, "Doug Mahugh" <Doug.Mahugh@microsoft.com> wrote:

    Regarding the invitation to the ODF TC, that was my idea.  I thought some might find it odd if we had an ODF-related event and didn't start by inviting the TC, and I wanted to make sure that everyone here knows they're welcome to attend if they're interested in learning more about our implementation.  We certainly weren't trying to gather the OASIS TC specifically -- I and others at Microsoft will be reaching out to others who have expressed an interest as well, but we started here.

    --
    **********************************************************************
    Senior Technical Evangelist - Adobe Systems, Inc.
    Duane's World TV Show - http://www.duanesworldtv.org/
    Blog - http://technoracle.blogspot.com
    Community Music - http://www.mix2r.com
    My Band - http://www.myspace.com/22ndcentury
    Adobe MAX 2008 - http://technoracle.blogspot.com/2007/08/adobe-max-2008.html
    **********************************************************************


  • 14.  RE: [office] Microsoft ODF workshop

    Posted 06-23-2008 23:55

    Thanks, Doug for that invitation and the additional information.

    I have received a note asking me why the TC was holding a meeting in Redmond and suggesting that doing so would be discriminatory to those who must  pay for their own travel, etc.  So I just want to clarify, that this is not an official ODF TC meeting that is being proposed.   Microsoft is inviting you, as individuals to attend their ODF event.  There is nothing wrong with that.  We (IBM) have discussed ODF at our Lotusphere convention, Sun (and others) talk about ODF at OpenOffice.org, etc.  There is absolutely nothing wrong with talking about ODF at your own corporate or organizational event.  

    Indeed I hope you all talk about ODF at every opportunity.  It is something you should be talking about.  Spread the word.

    -Rob



    Doug Mahugh <Doug.Mahugh@microsoft.com>

    06/23/2008 05:41 PM

    To
    "Charles-H. Schulz" <charles-h.schulz@arsaperta.com>, "office@lists.oasis-open.org" <office@lists.oasis-open.org>
    cc
    Subject
    RE: [office] Microsoft ODF workshop





    Hi Charles,

    The short answer to why this event is in Redmond is "that's where our ODF developers are."  We've received many questions recently about details of our planned ODF support, and we decided it would be good to have an event where we could explain our plans, show some pre-release code, and give people a chance to talk to the dev team about how we're approaching ODF support.  The only realistic way to do that is in Redmond, since that's where the dev/PM people are located.  It would be difficult to find a time when they could all be in another location (probably impossible until the next version of Office is close to shipping), so this Redmond event is the only way we can give everyone direct access to the people actually doing our ODF implementation work.

    Regarding the invitation to the ODF TC, that was my idea.  I thought some might find it odd if we had an ODF-related event and didn't start by inviting the TC, and I wanted to make sure that everyone here knows they're welcome to attend if they're interested in learning more about our implementation.  We certainly weren't trying to gather the OASIS TC specifically -- I and others at Microsoft will be reaching out to others who have expressed an interest as well, but we started here.

    Regarding Bruce's question on allowing virtual participation, I'm looking into some options for that and will share info when I have it.

    Regards,
    Doug





  • 15.  Re: [office] Microsoft ODF workshop

    Posted 06-25-2008 19:43
    Doug, Rob,
    
    Thank you for your answers. It obviously clarifies a lot of points.  
    I'm looking forward attending the virtual conference if that can be  
    done.
    
    Cheers,
    
    Charles-H. Schulz.
    
    Le 24 juin 08 à 01:55, robert_weir@us.ibm.com a écrit :
    
    >
    > Thanks, Doug for that invitation and the additional information.
    >
    > I have received a note asking me why the TC was holding a meeting in  
    > Redmond and suggesting that doing so would be discriminatory to  
    > those who must  pay for their own travel, etc.  So I just want to  
    > clarify, that this is not an official ODF TC meeting that is being  
    > proposed.   Microsoft is inviting you, as individuals to attend  
    > their ODF event.  There is nothing wrong with that.  We (IBM) have  
    > discussed ODF at our Lotusphere convention, Sun (and others) talk  
    > about ODF at OpenOffice.org, etc.  There is absolutely nothing wrong  
    > with talking about ODF at your own corporate or organizational event.
    >
    > Indeed I hope you all talk about ODF at every opportunity.  It is  
    > something you should be talking about.  Spread the word.
    >
    > -Rob
    >
    >
    >
    > Doug Mahugh 


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

    Posted 07-02-2008 15:46
    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