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


Help: OASIS Mailing Lists Help | MarkMail Help

office message

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

Subject: Re: [office] YEARFRAC, etc.

There are the four basis modes: US 30/360, Actual/Actual, Actual/360, Actual/365, and European 30/360.  The contradiction is in the Actual/Actual basis when applied to YEARFRAC.  We have two different approved DIS 29500 BRM resolutions which say two different things about how this is calculated.

As you say, one can make a choice that doesn't lead to a contradiction, such as using US 30/360, but that is a matter of user choice, or policy.  The user might reasonably require Actual/Actual to solve their problem, so we need to make sure we define this unambiguously.



04/05/2008 05:20 AM
Please respond to

office-formula@lists.oasis-open.org, robert_weir@us.ibm.com
Re: [office] YEARFRAC, etc.


I don't know about the final question but aren't these separate settings?

In other words, you choose one option or the other such that the options don't "contradict" each other?

Hope you are having a great day!


On Fri Apr 4 17:01 , robert_weir@us.ibm.com sent:

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:

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:
[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?]


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