[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: YEARFRAC, basis==0
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.