OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

# office message

[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

• From: "David A. Wheeler" <dwheeler@dwheeler.com>
• To: office@lists.oasis-open.org
• Date: Fri, 18 Apr 2008 18:00:46 -0400 (EDT)

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]