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

["Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca>]

On Mon, 2010-09-27 at 14:33 -0600, robert_weir@us.ibm.com wrote:

> 
> > 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

I first thought that that's what you meant, but the condition:
If A OR ( B AND (C OR (D AND E) ) ) then 
implies (A or B) ie. year(date1) == year(date2) or
year(date2)==year(date1)+1 

so your example does not apply.

In other words for date1 =2001-01-01 and date2= 2004-01-01
A=FALSE, B=FALSE, C=FALSE, D=TRUE, E=TRUE
so A OR ( B AND (C OR (D AND E) ) ) = FALSE, ie. 6 does not apply.

 

> 
> 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.

no since 6 does not apply, nor 7, nor 8, nor 9 procedure E would return
365 !?

> 
> 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

no: We have A=false; B=false; C=FALSE; D=FALSE; E=FALSE

so A OR ( B AND (C OR (D AND E) ) ) = FALSE

so 6 and 7 do not apply. 8 applies and yields 366 !?

> 
> 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.
> 

I suspect that there is something wrong with "If A OR ( B AND (C OR (D
AND E) ) )"

Andreas