Re: [office-formula] YEARFRAC again

[robert_weir@us.ibm.com]

For YEARFRAC(), don't we have a leap
year ambiguity as well?  For example, with a basis of 1, how do I
calculate the fraction of  a year between  Dec 31st 1999 and
January 1st 2000?  Is it 1/365?  Or 1/366?

Another function that is fraught with
peril is NETWORKDAYS().  Excel defines it as the number of working
days between two dates, excluding weekends, and optionally excluding an
array of holidays.  The problem is that the definition of "weekend"
is a cultural dependency.  In particular Muslim nations define the
weekend as being either Thursday/Friday or Friday/Saturday.  Having
this function just use the current user locale is insufficient, since you
may have a French office worker in Paris working on a spreadsheet describing
a project worked on by a subsidiary in Egypt.  We really need to have
something like a basis enumeration for weekend, maybe default is Sat/Sun,
but also allow Thus/Fri and Fri/Sat.  Does anyone know of there are
any other traditions? 

Microsoft has a technote on this issue,
with a suggested workaround, a rather hideous one in my mind:  http://support.microsoft.com/kb/821091/en-me

I think we can solve this one for real.

-Rob

Eike Rathke <erack@sun.com>

01/29/2007 11:54 AM

To	office-formula@lists.oasis-open.org
cc
Subject	Re: [office-formula] YEARFRAC again

Hi Andreas,

On Friday, 2007-01-26 13:50:34 -0700, Andreas J. Guelzow wrote:

> > 1. Documents exported to Excel using the new semantics don't
work there
> >    and at its best generate an error. Ok, nothing strange,
maybe users
> >    can live with it.
> > 
> > 2. Much worse, if Excel defined the same number with a different
meaning
> >    ex/imported documents silently work different. Most
certainly nothing
> >    users would like to live with.
> >    
> > We can prevent both by not defining new basis numbers.
> 
> Well, no, this does not prevent conflict. Applications will (and in
> other areas already have) define behaviours and functions that go
beyond
> what one can do in Excel. So there is a potential for conflict. Avoiding
> any specification for items not defined by Excel just means that we
add
> conflicts between other applications.
> 
> Since Excel does not support ODF we can in fact address the Excel
> compatibility when we open Excel files

That's easier said than done. While it works for single value arguments,
you can't remap an argument's value in an import filter if the argument
is calculated.

> so that is not a good reason to
> invite compatibility issues between applications such as Gnumeric.
> OpenOffice.org and KSpread!

Did any of these applications add a new basis number definition for
YEARFRAC so far? Is there a need for it?

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's
list.