Re: [office-formula] Reformulation of Day Count Bases

[robert_weir@us.ibm.com]

"Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca> 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, robert_weir@us.ibm.com 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
>