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


Help: OASIS Mailing Lists Help | MarkMail Help

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

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?



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