Re: [office-formula] YEARFRAC, etc.

[Eike Rathke <erack@sun.com>]

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.

PGP signature