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 21:07:50 -0400

```Hi Andreas,

I've uploaded a new yearfrac.odt and a spreadsheet with test cases.  Let's
see if this fixes it.

Taking the example from before:

Let date1 =2001-01-01 and date2= 2004-01-01

With basis =1 (Actual/Actual) we use Procedure B for the day count, and
Procedure E for days in year.

Day count is simple. It is the actual days = 1095 days.

Year count according to Procedure E replies on these tests:

1. Evaluate  A: year(date1) != year(date2) = TRUE
2. Evaluate  B: year(date2)!=year(date1)+1 = TRUE
3. Evaluate  C: month(date1) <=  month(date2) = TRUE
4. Evaluate  D: month(date1) !=  month(date2) FALSE
5. Evaluate  E: day(date1) <  day(date2) FALSE
6. Evaluate F: (A and B) or (A and C and D) or (A and C and E) = TRUE

So we then return according to step 7:

7. If F is true then return the average of the number of days in each year
between date1 and date2, inclusive.

So that is:

2001 has 365 days
2002 has 365 days
2003 has 365 days
2004 has 366 days

So average = (365+365+365+366)/4 = 365.25

So then YEARFRAC will return 1095/365.25 =   2.997946612 which is what
Excel gives.

Is that better?

Regards,

-Rob
```

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