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


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Re: [office-formula] YEARFRAC again

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.


Eike Rathke <erack@sun.com>

01/29/2007 11:54 AM

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?


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

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]