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

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

> 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


-----Original Message-----
From: David A. Wheeler [mailto:dwheeler@dwheeler.com]
Sent: Friday, June 13, 2008 9:35 AM
To: office-formula@lists.oasis-open.org
Subject: [office-formula] Re: Excel's YEARFRAC algorithm

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&#8217;s day-of-month is 30 and date2&#8217;s day-of-month is 31, date2&#8217;s day-of-month is changed to 30 (note that date2&#8217;s day-of-month will stay 31 if date1&#8217;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 &#8220;less than or equal to a year apart&#8221; (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 &#8220;less than or equal to a year apart&#8221; for purposes of this algorithm, one of these conditions much be true:
The two dates have the same year
Date2&#8217;s year is exactly one more than date1&#8217;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 &#8220;actual date&#8221; 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 &#8220;epoch&#8221;), and compute date2-date1.

To compute a date difference between two dates that use an &#8220;30 month day&#8221; 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.


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