"Andreas J. Guelzow" <> wrote on 09/24/2010
02:30:12 PM:
> Re: [office-formula] Reformulation of Day Count Bases
>
> On Wed, 2010-09-22 at 16:30 -0600, wrote:
> > http://www.oasis-open.org/committees/download.php/39507/YEARFRAC.odt
> >
>
> Just some observations:
>
> In Procedure A:
> - steps 2 and 3 should probably be switched
OK
> - step 4 is not needed since steps 5 and 6 will do that
>
Agreed.
> In Procedure C:
> - steps 2 and 3 should probably be switched
>
OK
> In Procedure E:
> - in step 6, "average number of days of the years between date1 and
> date2" sounds complicated since there are only 2 years involved. Do you
> mean "the average number of days in years year(date1) and year(date2)"?
>
For example, let date1 =2001-01-01 and date2= 2004-01-01
2001 has 365 days
2002 has 365 days
2003 has 365 days
2004 has 366 days
So average = 365.25
Check YEARFRAC(date1;date2;1) in that instance.
Procedure E says the answer would be equal to (365+365+365)/365.25 =
2.997946612, which is what Excel returns.
Maybe there is a better way to state that. Maybe, "the average of the
number of days in each of years between date1 and date2"?
> On second thought procedure E is strange:
> the days in year for date1=2001/1/1 and date2=2007/12/31 according to
> that procedure is 366. Do we really want that? (I had thought that
> actual/actual was reasonable).
>
So in your case the average year length would be 365 1/7 = 365.1428571
The number of days would be: 365 + 365 + 365 + 366 +365 + 365 +364 = 2556
So YEARFRAC in that case = 6.997261346, which matches Excel.
So I think we know what gives the right answer. I'll try to clarify the
definition in Procedure E.
> Andreas
>
>
> --
> Andreas J. Guelzow
> Concordia University College of Alberta
>
