Hi,
On Friday, 2008-04-04 17:01:11 -0400, Robert Weir wrote:
> 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.
> [...]
> 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].
Following the description of YEARFRAC given I'd say the year length used
for "start date in 2007 and end date in 2009" would be (365+366+365)/3,
and it looks like that would be the case, having drawn some samples of
those years.
Not being a native English speaker I guess that would be a correct
interpretation of "the average length of the years that the range
crosses, regardless of where start-date and end-date fall in their
respective years", or am I wrong?
Anyway, that's not what Excel calculates ...
Start date: 2007-12-31
End date: 2008-01-01
=YEARFRAC(start,end,1) => 0.00273973, which is identical to
1/((365+365)/2) instead of 1/((365+366)/2) that would be 0.00273978
With end:=2008-02-28 the result is 0.161643836, identical to
(end-start)/((365+365)/2)
With end:=2008-02-29 the result is 0.163934426, identical to
(end-start)/((366+366)/2) !!!
So "the years the range crosses" must be something different. It looks
like something "the years of where the range crossed February 28/29". It
gets totally weird though (Excel 2007):
Start End YEARFRAC equals
2007-02-27 2008-02-27 1 (end-start)/((365+365)/2)
2007-02-27 2008-02-28 1.001367989 (end-start)/((365+366)/2)
2007-02-27 2008-02-29 1.004103967 (end-start)/((365+366)/2)
2007-02-27 2008-03-01 1.006839945 (end-start)/((365+366)/2)
2007-02-28 2008-02-27 0.997260274 (end-start)/((365+365)/2)
2007-02-28 2008-02-28 1 (end-start)/((365+365)/2)
2007-02-28 2008-02-29 1.001367989 (end-start)/((365+366)/2)
2007-02-28 2008-03-01 1.004103967 (end-start)/((365+366)/2)
2007-03-01 2008-02-27 0.994520548 (end-start)/((365+365)/2)
2007-03-01 2008-02-28 0.997260274 (end-start)/((365+366)/2)
2007-03-01 2008-02-29 0.997267760 (end-start)/((366+366)/2) !!!
2007-03-01 2008-03-01 1 (end-start)/((366+366)/2) !!!
So, average yes, years that the range crosses no.
Eike
--
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets. --Robert Weir on the OpenDocument formula subcommittee's list.
