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

# office-formula message

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

Subject: Re: Excel's YEARFRAC algorithm

• From: "David A. Wheeler" <dwheeler@dwheeler.com>
• To: office-formula@lists.oasis-open.org
• Date: Fri, 13 Jun 2008 12:34:36 -0400 (EDT)

```Doug Mahugh:
> Attached are two programs that demonstrate the algorithm Excel uses for
> the YEARFRAC function.

Excellent!  This is very useful information - thanks for taking the time to make it available to us!

For comparison purposes, here is the description and Python code that I _believe_ implements the same algorithm.  By cross-comparing, we should gain much more confidence in it.  You can get more info at: http://www.dwheeler.com/yearfrac

I presume that Microsoft does NOT plan on changing this algorithm?  Because if they do, any program that uses "Basis" could produce a different answer (and cause big \$ changes).  I know that the draft OOXML rules are different; our current plan was to use the "current Excel rules" for basis 0-5, and permit "add 32" for those who want to implement the OOXML algorithms.

--- David A. Wheeler

YEARFRAC takes two dates, determines the &#8220;number of days different&#8221;, and divides that by the &#8220;number of days in the year&#8221;.  Its interpretation of these two terms depends on the &#8220;basis&#8221; value, which is defined as follows:

Basis 0 or omitted: 30/360.  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference of days is 0.  Assumes that each month has 30 days and the total number of days in the year is 360 by making the following adjustments:
If both day-of-months are 31, they are changed to 30
Otherwise, if date1&#8217;s day-of-month is 31, it is changed to 30
Otherwise, if date1&#8217;s day-of-month is 30 and date2&#8217;s day-of-month is 31, date2&#8217;s day-of-month is changed to 30 (note that date2&#8217;s day-of-month will stay 31 if date1&#8217;s day < 30)
Otherwise, if both dates are the last day of February in their respective years, both day-of-month is changed to 30
Otherwise, if date1 is the last day of February, its day-of-month is changed to 30

Basis 1: Actual/actual.  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference in days is 0.  If date1 and date2 not &#8220;less than or equal to a year apart&#8221; (as defined below), then the days in the years between the dates is the average number of days in the years between date1 and date2, inclusive.  Otherwise, the days in the years between the dates is 365, except for these cases (where it is 366):  the dates are in the same year and it is a leap-year, a February 29 occurs between the two dates, or date2 is February 29.

To determine if date1 and date2 are &#8220;less than or equal to a year apart&#8221; for purposes of this algorithm, one of these conditions much be true:
The two dates have the same year
Date2&#8217;s year is exactly one more than date1&#8217;s year, and ((date1.month > date2.month) or ((date1.month == date2.month) and (date1.day >= date2.day)))

Basis 2: Actual/360. Computes the actual difference in days, and presumes there are always 360 days per year.

Basis 3: Actual/365. Computes the actual difference in days, and presumes there are always 365 days per year.

Basis 4: 30/360.  Truncates date values and swaps them if date1 is after date2.  If the dates are equal, the difference of days is 0.  Assumes that each month has 30 days and the total number of days in the year is 360; any day-of-month (in date1, date2, or both) with a value of 31 is changed to 30.  Note that February dates are never changed, because there is no February 31.

To compute a date difference between two dates that use an &#8220;actual date&#8221; system (basis 1, 2, and 3), perform the swapping as discussed above (so date2 is always after date1),  consider both dates as the number of days after the same fixed date (the &#8220;epoch&#8221;), and compute date2-date1.

To compute a date difference between two dates that use an &#8220;30 month day&#8221; system (basis 0 and 4), first perform modifications described above (so date2 is always after date1), and then compute the difference as (date2.year*360+date2.month*30+date2.day) - (date1.year*360 + date1.month*30 + date1.day).

Here is pseudocode that implements the derived definitions for YEARFRAC (if these differ from the text above, the pseudocode is correct; please contact the author of any differences):

def appears_le_year(date1, date2):
# Returns True if date1 and date2 "appear" to be 1 year or less apart.
# This compares the values of year, month, and day directly to each other.
# Requires date1 <= date2; returns boolean.  Used by basis 1.
if date1.year == date2.year:
return True
if (((date1.year + 1) == date2.year) and
((date1.month > date2.month) or
((date1.month == date2.month) and (date1.day >= date2.day)))):
return True
return False

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 == 31: date1day = 30
elif (date1day == 30 and date2day == 31): date2day = 30
# Note: If date2day==31, it STAYS 31 if date1day < 30.
# Special fixes for February:
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 # "Illegal" Feb 30 date.
daydiff360 = ( (date2day + date2month * 30 + date2year * 360) -
(date1day + date1month * 30 + date1year * 360))
return daydiff360 / 360.

def basis1(date1,date2):
# Swap so date1 <= date2 in all cases:
if date1 > date2:
date1, date2 = date2, date1
if date1 == date2:
return 0.0
if appears_le_year(date1, date2):
if (date1.year == date2.year and is_leap_year(date1.year)):
year_length = 366.
elif (feb29_between(date1, date2) or
(date2.month == 2 and date2.day == 29)): # fixed, 2008-04-18
year_length = 366.
else:
year_length = 365.
return diffdays(date1, date2) / year_length
else:
num_years = (date2.year - date1.year) + 1
days_in_years = diffdays(date(date1.year, 1, 1), date(date2.year+1, 1, 1))
average_year_length = days_in_years / num_years
return diffdays(date1, date2) / average_year_length

def basis2(date1,date2):
# Swap so date1 <= date2 in all cases:
if date1 > date2:
date1, date2 = date2, date1
return diffdays(date1, date2) / 360.

def basis3(date1,date2):
# Swap so date1 <= date2 in all cases:
if date1 > date2:
date1, date2 = date2, date1
return diffdays(date1, date2) / 365.

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. 28 or 29 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]