RE: [office-formula] 6.9.13 NETWORKDAYS

[Eric Patterson <ericpa@exchange.microsoft.com>]

Looking at NETWORKDAYS further, I see that the 4th parameter, Workdays is defined as a list of logical values for the days of week, starting on SUNDAY.

ISO8601 defines a calendar week to be a "time interval of seven calendar days starting with a Monday".

I propose that OpenFormula should use the same convention in this case.

The amended test cases would be as follows.  Note, the results of the last 2 were incorrect in the current draft.

Expression                                                                                              Result
=NETWORKDAYS(DATE(2007;1;1),DATE(2007;1;12))                                            10
=NETWORKDAYS(DATE(2007;1;1),DATE(2007;1;12);DATE(2007;1;1))                             9
=NETWORKDAYS(DATE(2007;1;1),DATE(2007;1;14);DATE(2007;1;6))                             10
=NETWORKDAYS(DATE(2007;1;1),DATE(2007;1;14);;{0;0;0;0;1;1;0})                   10
=NETWORKDAYS(DATE(2007;1;1),DATE(2007;1;14);DATE(2007;1;6);{0;0;0;0;1;1;0})     10

-Eric

-----Original Message-----
From: robert_weir@us.ibm.com [mailto:robert_weir@us.ibm.com]
Sent: Monday, February 23, 2009 7:04 PM
To: office-formula@lists.oasis-open.org
Subject: Re: [office-formula] 6.9.13 NETWORKDAYS

Eric Patterson <ericpa@exchange.microsoft.com> wrote on 02/23/2009
07:47:51 PM:

>
> Reading the definition of the NETWORKDAYS() function in the current
> draft of the specification, I see that the function definition
> includes an optional 4th parameter.  Microsoft Excel's definition of
> this function only includes 3 parameters.
>
> I'm trying to understand the implications of this.  If the intent of
> OpenFormula is to create a portable syntax how should differences
> like this be handled?  Implementers that only use the first 3
> parameters could share documents, but what is expected if the 4th
> parameter is included in a document?  I would expect it to return an
> error value to provide a warning to the user.
>
> What effect would implementing only the first 3 parameters have on
> application and document conformance?
>

The level of granularity is the function.  Think of it like a .NET
interface.  You either implement it or you don't.  The contract is
IEnumerable and if you want to say you implement IEnumerable then you
implement all of IEnumerable.  Same with NETWORKDAYS().  You either
implement it or you don't.  There is no 75% conformance at the level of a
function.

IMHO, user expectations are (in order):

1) That the spreadsheet will calculate properly in Excel, to give the same
results it did in the original application.

2) That if Excel lacked a particular function or parameter in the
function, that it would fail in an obvious (noticeable) way rather than
silently give a wrong answer.

Of course, you are free to be conformant and meet user expectations at the
same time.  Simply add support for that 4th parameter.  I know we've heard
from users, especially in the Middle East, that the work-week assumptions
of the 3-parameter Excel function did not work well for them, since their
legally-defined weekend was Th/Fr or Fr/Sa and the 3-parameter function
would yield incorrect results for them.

-Rob



---------------------------------------------------------------------
To unsubscribe from this mail list, you must leave the OASIS TC that
generates this mail.  Follow this link to all your TCs in OASIS at:
https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php