office message
[Date Prev]
| [Thread Prev]
| [Thread Next]
| [Date Next]
--
[Date Index]
| [Thread Index]
| [List Home]
Subject: Re: [office] YEARFRAC, etc.
- From: robert_weir@us.ibm.com
- To: patrick@durusau.net
- Date: Sat, 5 Apr 2008 14:18:22 -0400
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.
-Rob
<patrick@durusau.net>
04/05/2008 05:20 AM
Please respond to
patrick@durusau.net |
|
To
| office-formula@lists.oasis-open.org,
robert_weir@us.ibm.com
|
cc
| office@lists.oasis-open.org
|
Subject
| Re: [office] YEARFRAC, etc. |
|
Rob,
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!
Patrick
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:
- 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]