Re: [office] Excel 2007 != Ecma spec YEARFRAC. Not even slightly. What should we do?

["Warren Turkal" <turkal@google.com>]

On Fri, Apr 18, 2008 at 7:00 AM, David A. Wheeler <dwheeler@dwheeler.com> wrote:
>  The problem is that basis values need not be a constant in the cell; it might be
>  from another cell and/or a calculation (directly or indirectly), e.g.:
>  YEARFRAC([.A5];[.B5];[.C5]+1)

This seems like an unrealistic use case. In all sincerity, why would
someone do that? Is it common? I just don't see folks selecting the
basis by saying I want whatever the basis is identified by taking the
identifier of some basis (e.g. actual/actual) and adding 1 to it. I
cannot think of any valid use of that technique.

Having said that. If people do it, import filters can just map it to
whatever fixed string it's value corresponds to on import. It's not
that big of a deal. Just map the basis IDs to strings. Maybe you would
vary the map depending the version of the program that produces the
file being imported.

>  I'd expect most basis values to be embedded constants.  But if we can make life easier for
>  those doing transitions, or having to convert back-and-forth, we should try.

The help that we provide should be a consistent representation of
various algorithms. For instance,
YEARFRAC(A5;B5;"msexcel_2007_actual_actual") would be the
representation for calculating YEARFRAC with the reverse engineered MS
Excel 2007 Actual/Actual basis algorithm. If you want to only specify
the basis in one place, create another sheet and store it there. For
example:

Config.A1="msexcel_2007_actual_actual"
Calc.A5=a date
Calc.B5=b date
Calc.C5==YEARFRAC(A5;B5;Config.A1)

Spreadsheet programs could provide an interface to alter the basis of
all formulas if they wanted. They could even ask whether the user
wants to emulate the basis calculations of the spreadsheet being
imported on opening the file.

wt