[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

**References**:**Groups - YEARFRAC.odt uploaded***From:*robert_weir@us.ibm.com

**Reformulation of Day Count Bases***From:*robert_weir@us.ibm.com

**Re: [office-formula] Reformulation of Day Count Bases***From:*"Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca>

**Re: [office-formula] Reformulation of Day Count Bases***From:*robert_weir@us.ibm.com

**Re: [office-formula] Reformulation of Day Count Bases***From:*"Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca>

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