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

*Subject*: **Re: [office-formula] YEARFRAC, etc.**

*From*:**Eike Rathke <erack@sun.com>***To*: office-formula@lists.oasis-open.org, office@lists.oasis-open.org*Date*: Mon, 7 Apr 2008 17:50:50 +0200

Hi, On Friday, 2008-04-04 17:01:11 -0400, Robert Weir wrote: > For YEARFRAC, here is their new definition that explains how leap years > are handled: > > The fractional number of years represented by the number of whole days > between two dates, start-date and end-date., according to basis. If the > Actual/actual basis is used, the year length used is the average length of > the years that the range crosses, regardless of where start-date and > end-date fall in their respective years. > [...] > 1 == Actual/actual. > > The actual number of days between the two dates are counted. If the date > range includes the date 29 February, the year is 366 days; otherwise it is > 365 days. > > [Rob's observation -- Yes, this does contradict what they say for > YEARFRAC, where they say the year length is the average of the years in > the ranges crossed. I guess we'll need to test this to find out which is > really correct]. Following the description of YEARFRAC given I'd say the year length used for "start date in 2007 and end date in 2009" would be (365+366+365)/3, and it looks like that would be the case, having drawn some samples of those years. Not being a native English speaker I guess that would be a correct interpretation of "the average length of the years that the range crosses, regardless of where start-date and end-date fall in their respective years", or am I wrong? Anyway, that's not what Excel calculates ... Start date: 2007-12-31 End date: 2008-01-01 =YEARFRAC(start,end,1) => 0.00273973, which is identical to 1/((365+365)/2) instead of 1/((365+366)/2) that would be 0.00273978 With end:=2008-02-28 the result is 0.161643836, identical to (end-start)/((365+365)/2) With end:=2008-02-29 the result is 0.163934426, identical to (end-start)/((366+366)/2) !!! So "the years the range crosses" must be something different. It looks like something "the years of where the range crossed February 28/29". It gets totally weird though (Excel 2007): Start End YEARFRAC equals 2007-02-27 2008-02-27 1 (end-start)/((365+365)/2) 2007-02-27 2008-02-28 1.001367989 (end-start)/((365+366)/2) 2007-02-27 2008-02-29 1.004103967 (end-start)/((365+366)/2) 2007-02-27 2008-03-01 1.006839945 (end-start)/((365+366)/2) 2007-02-28 2008-02-27 0.997260274 (end-start)/((365+365)/2) 2007-02-28 2008-02-28 1 (end-start)/((365+365)/2) 2007-02-28 2008-02-29 1.001367989 (end-start)/((365+366)/2) 2007-02-28 2008-03-01 1.004103967 (end-start)/((365+366)/2) 2007-03-01 2008-02-27 0.994520548 (end-start)/((365+365)/2) 2007-03-01 2008-02-28 0.997260274 (end-start)/((365+366)/2) 2007-03-01 2008-02-29 0.997267760 (end-start)/((366+366)/2) !!! 2007-03-01 2008-03-01 1 (end-start)/((366+366)/2) !!! So, average yes, years that the range crosses no. Eike -- Automatic string conversions considered dangerous. They are the GOTO statements of spreadsheets. --Robert Weir on the OpenDocument formula subcommittee's list.

**References**:**YEARFRAC, etc.***From:*robert_weir@us.ibm.com

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