OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

office-formula message

[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

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