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, etc.

• From: robert_weir@us.ibm.com
• To: office-formula@lists.oasis-open.org
• Date: Fri, 4 Apr 2008 17:01:11 -0400

Here is some excerpts from Ecma's revised language for spreadsheet functions in OOXML.  This should be their final text.

For YEARFRAC, here is their new definition that explains how leap years are handled:

The fractional number of years represented by the number of whole days between two dates, start-date and end-date., according to basis. If the Actual/actual basis is used, the year length used is the average length of the years that the range crosses, regardless of where start-date and end-date fall in their respective years.   However, if the value of basis is out of range, #NUM! is returned.

[Rob's observation -- I wouldn't count on this definition for dates ranges in 1900, especially ones before February 28th.  There are probably some Excel leap year bug interactions there that are not fully defined in OOXML].

And here is the new language for the date counting conventions:

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.

1 == Actual/actual.

The actual number of days between the two dates are counted. If the date range includes the date 29 February, the year is 366 days; otherwise it is 365 days.

[Rob's observation -- Yes, this does contradict what they say for YEARFRAC, where they say the year length is the average of the years in the ranges crossed.  I guess we'll need to test this to find out which is really correct].

2 == Actual/360.

Similar to Basis 1, but only has  360 days per year.

3 == Actual/365.

Similar to Basis 1, but always has 365 days per year.

4 == European 30/360.

The European method for adjusting day counts. 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, all dates with a day value of 31 are changed to day 30, including 28 and 29 February.
[Rob's observation -- Your guess is as good as mine on that second bullet.  Can 28 February or 29 February ever have a date value of 31?  I'm having difficulties parsing this in any way that makes sense.  Anyone else have better luck?]

-Rob

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