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: YEARFRAC, basis==0

• From: "David A. Wheeler" <dwheeler@dwheeler.com>
• To: office@lists.oasis-open.org
• Date: Thu, 17 Apr 2008 12:09:19 -0400 (EDT)

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

```

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]