[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Good news: All Excel 2007 basis 0..4 of YEARFRAC explained
More good news! We now have an algorithm for basis=4, so we have algorithms for ALL basis values of Excel 2007's YEARFRAC. Patrick Durusau worked out a draft set of rules for basis=4. They didn't QUITE work with the full dataset, but I managed to tweak them and MAKE them work. I've posted everything here: http://www.dwheeler.com/yearfrac A big THANKS to Patrick Durusau, who plowed through the various datapoints to help determine what Excel's REAL algorithm was. Basis=4 doesn't match its spec either. A few details below. --- David A. Wheeler === Details === Excel 2007's basis=4 _also_ does not match the spec. The spec says that it adjusts Feb 28 and 29, but Wheeler found that in fact it never does (!). For example: Excel 2007 produces: 1999-01-05 2000-02-28 4 1.147222222 but the spec would produce: ((2000*360 + 2*30 + 30) - (1999*360 + 1*30 + 5))/360. = 1.1527777777777777 Excel produces: 1999-01-05 2000-02-29 4 1.15 but the spec would produce: ((2000*360 + 2*30 + 30) - (1999*360 + 1*30 + 5))/360. = 1.1527777777777777 Algorithm: 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. at ALL daydiff360 = ( (date2day + date2month * 30 + date2year * 360) - (date1day + date1month * 30 + date1year * 360)) return daydiff360 / 360.
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]