[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

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

*From*:**"Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca>***To*: "robert_weir@us.ibm.com" <robert_weir@us.ibm.com>*Date*: Mon, 27 Sep 2010 15:03:47 -0600

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

**Follow-Ups**:**Re: [office-formula] Reformulation of Day Count Bases***From:*robert_weir@us.ibm.com

**Re: [office-formula] Reformulation of Day Count Bases***From:*robert_weir@us.ibm.com

**References**:**Groups - YEARFRAC.odt uploaded***From:*robert_weir@us.ibm.com

**Reformulation of Day Count Bases***From:*robert_weir@us.ibm.com

**Re: [office-formula] Reformulation of Day Count Bases***From:*"Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca>

**Re: [office-formula] Reformulation of Day Count Bases***From:*robert_weir@us.ibm.com

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]