Re: [office-formula] YEARFRAC again

[Eike Rathke <erack@sun.com>]

Hi robert_weir,

On Monday, 2007-01-29 12:25:31 -0500, robert_weir@us.ibm.com wrote:

> 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?

If I understood correctly, according to
http://www.isda.org/c_and_a/pdf/mktc1198.pdf and just in case the basis
number 1 actual/actual really refers to the AFB actual/actual (would be
different if it referred ISDA or ISMA actual/actual) there shouldn't be
any ambiguity, as the 29th of February isn't included in the period of
this example, and the return value should be 1/365, which more or less
(to some decimals) is also what Excel returns. OOoCalc returns 1/366,
which is wrong, I think.


> 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.

For the Jewish calendar it's Friday/Saturday as well.

> 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? 

I could bet there are also countries where a weekend doesn't consist of
2 days off.. I would refrain from pressing other semantics into the
NETWORKDAYS function, the holiday-array parameter is already optional
and if you add yet another parameter you may get interoperability
problems. If needed it would be better to have another function we could
define as we like.

> 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

Quite ugly..

  Eike

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