YEARFRAC, basis==0

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

In my previous post, I noted that YEARFRAC with basis==0 was radically
different between Excel 2007 and the Ecma spec.  Any difference is bad,
but this is the DEFAULT basis for many functions.

For completeness, I will document the differences here for basis==0.

--- David A. Wheeler


=================================================

Ecma says that Basis 0 (default) == US (NASD) 30/360:
"Assumes that each month has 30 days and the total number of days in the
year is 360 by making the following adjustments:
If the date is 28 or 29 February, it is adjusted to 30 February.
For months with 31 days, if the first date has a day value of 31, the date
is converted to day 30. If the second date has a day value of 31, it is
changed to 30 days as long as the first date was not 28 or 29 February, in
which case it does not change."

I can easily prove that Excel 2007 does NOT meet this spec.
It'd be a good idea for Excel 2007 to support US (NASD) 30/360, but Excel
does not actually do so - at least not with basis=0.

For example, Excel 2007 takes 2000-01-01...2000-01-31
in basis 0 and returns "0.083333333", which is 30/360.
The Ecma rules would produce 0.080555555555555561 (29/360.).
In other words, Excel DOES NOT change Jan. 31 into Jan. 30.
For another example,
 2000 01 01 - 2000 02 28 basis=0 gives (in Excel 2007) 0.158333333
  which is 57/360, but the Ecma rule would have you do compute
"2000 01 01 to 2000 02 30" which would be 59/360.

I have developed an algorithm that gives the same answers as Excel 2007 does
for basis==0 for a test suite.  That test suite has some known holes that are important
to this algorithm; I am working to get a complete test suite, which I can then use
to refine this algorithm.
Nevertheless, here's the best algorithm I have that reproduces Excel 2007's YEARFRAC
basis==0 behavior:

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 == 30 and date2day == 31): date2day = 30
  elif date1day == 31: date1day = 30
  # Special fixes for Feb - this may create an "illegal" date
  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

  daydiff360 = ( (date2day + date2month * 30 + date2year * 360) -
                 (date1day + date1month * 30 + date1year * 360))
  return daydiff360 / 360.



Note that this is remarkably different than what the Ecma spec claims,
producing many differences.