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


Help: OASIS Mailing Lists Help | MarkMail Help

office message


Subject: Re: [office] YEARFRAC, etc.

Bob Jolliffe:
> It seems to me that YEARFRAC is quite a ridiculous notion for a function - in a number of ways it defies definition.  The only sane reason to have such a function is for "backward compatibility" with a rather idiosyncratic Excel.

No, YEARFRAC and friends are VITALLY CRITICAL for financial calculations.  If you wish to use a spreadsheet program for financial calculations involving dates - and that's a common use case - it's quite likely that you WILL need these functions.

It does not "defy definition", either.

The problem is that there is no authoritative source for DOCUMENTATION of these definitions as they are used in spreadsheets.  There are a LOT of definitions of "year fractions", but no authoritative mapping of the basis numbers to any particular definition, or even assurance that the ones that ARE used are published anywhere.   There _is_ an "authoritative source" in the sense that "you won't lose your job if you use that answer", and the answer is "use Excel".  From a standards-setting point of view, that is a complete cop-out.  But to business users, they simply need a justifiable rationale for a calculation, and "I used Excel" is (to them) perfectly acceptable (they are using a "trusted third party").  Business users do NOT require a rigorous mathematical definition; they just require a way to justify their decisions.  Almost all of them are unaware that different versions of Excel give different answers for YEARFRAC, and it's not clear it would matter to them (!).  Obviously, it should be defined in detail in Microsoft XML (a clearer name for their spec), but that spec does not do so. (That specification fails to meet even minimal standards for quality, but I digress.)

Let me clarify the calculator tale, because it illustrates the issue well.  I have a family relation who did some mortgage calculations, and he found that DIFFERENT calculators produced DIFFERENT results in a particular case.  He and his co-workers did NOT know why they were different. Was their solution "figure it out"?  No.  Instead, they decided that HP was the most trusted vendor of the different calculators, and thus decided to use the HP calculator's answers.  It's not that they "like" the HP calculator answers, it's that they do not feel qualified to determine which answers were correct.  Instead, they base their decisions on "trust of organization" instead of some complex mathematical analysis.  I do not think this is an isolated case.  Business users often don't look for DOCUMENTS (like specs), they look for ORGANIZATIONS/PEOPLE that they trust most in order to make their decisions.

Anyone familiar with standards will see the problems with this approach that many business users take.  If Microsoft changes their code (and they have), then YEARFRAC and friends will compute different answers (and they do), which could cause a lot of problems down the road (and I suspect that they do!).  What would be wiser would be to document the function, but it's been difficult to get this kind of information anywhere.

There are two solutions I see:
1. Incompletely document the function.  That is what Microsoft XML does (in this area as well as many others).  If Microsoft XML becomes an ISO standard (it HAS been recently accepted by ISO voting) then obviously fully defining this function and related functions is not really necessary for ISO approval.  That is a complete cop-out, of course, but if ISO member countries do not feel that quality in this area is important, then perhaps we are putting ourselves to an excessively high quality standard. Anyone who approved Microsoft XML should, as a consequence, automatically approve of this approach (since they also approved of Microsoft XML).    I am not happy with that answer.
2. Determine the full documentation of the function.   Since it's not documented, this involves comparing massive numbers of test cases (rigged to cover all corner cases) to Excel answers.  I have begun attempting to do this myself, but this takes a great deal of time.  If anyone could spend time to help me do this, I would greatly appreciate it.  If no one is willing, and I can't generate enough time, then back off to #1.

--- David A. Wheeler
