[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Re: [office-formula] YEARFRAC again
Hi robert_weir, On Monday, 2007-01-29 12:25:31 -0500, email@example.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.