This directory contains files for figuring out the "YEARFRAC" issues in OpenFormula. The file yearfrac_data_basis_all.zip contains the YEARFRAC test cases. These are files "yearfrac_data_basisX.txt" which give results from MS Excel 2007, where X is the basis value (0 through 4, inclusive). The test case expected values were sent to me as series of messages, with one at: Thu, 26 Jul 2007 22:23:29 +0800, combined with later test cases generated April 2008. Wheeler created the spec for what test cases should be used, and other committee members actually obtained the information that make up the test cases (THANK YOU!). Wheeler then created the programs to post-process the data. The number of test cases in each one is as follows: 6574965 yearfrac_data_basis0.txt 6574582 yearfrac_data_basis1.txt 6574770 yearfrac_data_basis2.txt 6574401 yearfrac_data_basis3.txt 6574976 yearfrac_data_basis4.txt 32873694 total Duplicates were eliminated using string comparisons, so there are a few numeric duplicates (e.g., where 0 and 0.00000 are the expected answers). This causes no harm. Useful sources of date calculation info are: http://www.isda.org/c_and_a/pdf/mktc1198.pdf http://en.wikipedia.org/wiki/Day_count_convention On Monday, 2008-04-14 18:47:02 -0400, David A. Wheeler wrote: > Other sources are, of course, open source implementations' code. Here is KOffice's: > http://api.kde.org/koffice-api/koffice-apidocs/kspread/html/helper_8cpp-source.html#l00194 > > I'm sure our friends from OpenOffice.org and others can point to similar files for their code, > in those cases where it's publicly accessible. Sure, for OOo it's http://sc.openoffice.org/source/browse/sc/scaddins/source/analysis/analysishelper.cxx?view=markup in GetDiffParam(). Note that implementation is known to produce some few results different from Excel. The GetDiffDate360() function for the basis 0/4 cases may have to be aligned to the internal DAYS360 spreadsheet function that is implemented in http://sc.openoffice.org/source/browse/sc/sc/source/core/tool/interpr2.cxx?view=markup method ScInterpreter::ScGetDiffDate360() and just recently got slightly modified to produce identical results to Excel. As that's not integrated to HEAD yet see http://sc.openoffice.org/source/browse/sc/sc/source/core/tool/interpr2.cxx?rev=1.35.20.1&view=markup and for reasoning and test cases see http://qa.openoffice.org/issues/show_bug.cgi?id=84934 I'll add some details about DAYS360 to my working copy of our draft, which btw I plan to upload to the OASIS repository by the end of this week or next week. Eike On Fri Apr 4 17:01 , 2008, robert_weir at us.ibm.com sent: Here is some excerpts from Ecma's revised language for spreadsheet functions in OOXML. This should be their final text. 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. However, if the value of basis is out of range, #NUM! is returned. [Rob's observation -- I wouldn't count on this definition for dates ranges in 1900, especially ones before February 28th. There are probably some Excel leap year bug interactions there that are not fully defined in OOXML]. And here is the new language for the date counting conventions: 0 (default) == US (NASD) 30/360. Assumes that each month has 30 days and the total number of days in the year is 360 by making the following adjustments: If the date is 28 or 29 February, it is adjusted to 30 February. For months with 31 days, if the first date has a day value of 31, the date is converted to day 30. If the second date has a day value of 31, it is changed to 30 days as long as the first date was not 28 or 29 February, in which case it does not change. 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]. 2 == Actual/360. Similar to Basis 1, but only has 360 days per year. 3 == Actual/365. Similar to Basis 1, but always has 365 days per year. 4 == European 30/360. The European method for adjusting day counts. Assumes that each month has 30 days and the total number of days in the year is 360 by making the following adjustments: If the date is 28 or 29 February, it is adjusted to 30 February. For months with 31 days, all dates with a day value of 31 are changed to day 30, including 28 and 29 February. [Rob's observation -- Your guess is as good as mine on that second bullet. Can 28 February or 29 February ever have a date value of 31? I'm having difficulties parsing this in any way that makes sense. Anyone else have better luck?] -Rob OpenOffice.org's related discussions: http://www.openoffice.org/issues/show_bug.cgi?id=84934 Note that YEARFRAC in Excel has changed. Quoting: http://ewbi.blogs.com/develops/2003/10/excel_yearfrac_.html 2003.10.22 03:43 PM Excel 97/2002 YEARFRAC Difference It seems the Excel YEARFRAC function, which is part of the Analysis Toolpak add-in, returns different results in Excel 97 and 2002 when used with the US (NASD) 30/360 basis option over certain dates. Using this formula: =YEARFRAC(DATE(2002, 2, 28),DATE(2002, 12, 31),0) In Excel 97 SR-2 (I) we get this: 0.833333333 But in Excel 2002 SP-2, we get this: 0.836111111 That's a difference of about a day, which is pretty serious in the context in which we were using it. A quick search of the MS Knowledge Base found no relevant articles, and I can't find anyone reporting this particular YEARFRAC problem on the newsgroups via Google, though there have been numerous problems with YEARFRAC and other Excel date/time functions as a result of Y2K and leap years. I posted this as a question to the microsoft.public.excel.worksheet.functions newsgroup, but haven't yet received a reply. Here's the only real definition of the US (NASD) 30/360 basis method I've been able to find (you'll have to navigate yourself to the Date & Time > YEARFRAC entry, as the frame is redirecting deep links to its home page). ...