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: robert_weir@us.ibm.com
• To: "Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca>
• Date: Mon, 27 Sep 2010 16:33:22 -0400

```"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:
> >
>
> 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
>

```

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