From patrick@durusau.net Fri May 25 05:15:50 2007 MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Date: Fri, 25 May 2007 08:15:19 -0400 From: Patrick Durusau Subject: YEARFRAC To: Dave Welsh , Doug Mahugh , Frank Farance , Jerry Smith , Jon Bosak , "Levine, Leonard F CIV DISA GES-E" , Norm Walsh , "Lynne A. Price" , Rex Jaeschke , Richard Barber , Robert Weir , Steve Carton , Tim Schoechle , "Mason, James David (MXM)" , Kevin Trainor , Eduardo Gutentag , Ron Silletti Reply-to: patrick@durusau.net X-Accept-Language: en-us, en X-PMX-Version: 5.2.0.264296 X-ASG-Debug-ID: 1180095338-2ba000030000-F3fpwQ X-Barracuda-URL: http://10.10.100.8:8000/cgi-bin/mark.cgi X-Barracuda-Connect: unknown[66.110.197.207] X-Barracuda-Start-Time: 1180095338 X-ASG-Whitelist: Client X-ASG-Orig-Subj: YEARFRAC X-Barracuda-Virus-Scanned: by Barracuda Spam Firewall at insimail.com Greetings! Some time ago I asked Doug about what appears to be an under definition on the function YEARFRAC. The text for that function in DIS 29500 is the same as what appears in the draft ODF Formula work. The issue is a setting that is: NASD 30/360 and European 30/360 and it is said to give different results. Everyone agrees that there are different results but no one could tell me why. Seems like there must be a rule. And there is! I followed some references sent to me by David Wheeler (formula work in ODF) and found the following: > US (NASD) 30/360: If start_date is the 31st of a month, it's set to > the 30th of the same month. If end_date is the 31st of a month, it's > set to the 30th of the same month unless start_date is less than the > 30th of a month, in which case end_date is set to the 1st of the next > month. > > The denominator used to compute the fraction is 360. > > and, > > European 30/360: Start and end dates that occur on the 31st of a month > are set to the 30th of the same month. The denominator used to compute > the fraction is 360. But as far as I can tell, neither DIS 29500 nor the ODF formula work has a pointer at YEARFRAC or any other place where this comes up as to why the results are different. So, just as an editorial matter, I think when the text is on its face confusing, such as suggesting the same inputs lead to a different result, that some pointer to why the results are different, etc. should be included in the text. I will be casting this into a formal comment but was so happy to find that there was an explanation for an accounting function that I wanted to share it with the group. Hope everyone is looking forward to a great weekend! Patrick PS: The ODF formula group suggested dealing with this issue with "test cases" which I vehemently oppose. If you can't state a rule in such cases then you don't understand what is being standardized well enough to be saying anything at all. -- Patrick Durusau Patrick@Durusau.net Chair, V1 - Text Processing: Office and Publishing Systems Interface Co-Editor, ISO 13250, Topic Maps -- Reference Model Member, Text Encoding Initiative Board of Directors, 2003-2005 Topic Maps: Human, not artificial, intelligence at work!