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]