[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Re: [office-formula] Reformulation of Day Count Bases
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
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]