GNOME Bugzilla – Bug 172458
NETWORKDAYS assumes Monday-Friday workweek
Last modified: 2010-07-03 19:32:40 UTC
It looks like the functions do not take into account the locale of the machine. So, for Israel, the return for =NETWORKDAYS(DATE(2005,4,1),DATE(2005,4,30)) should be 20 and not 21. Like so, =WORKDAY(DATE(2005,4,1),10) should return 14/04/2005 and not 15/04/2005. I only hope that you do not reproduce the same bug in the same named functions of Excel. I used the Windows distribution package, with a default installation procedure.
Why do you think =NETWORKDAYS(DATE(2005,4,1),DATE(2005,4,30)) should be 20? If I read the docs coorectly, you are supposed to specify which days to count as holidays.
The function =NETWORKDAYS may receive 3 parameters: 1. the start date; 2. the end date; 3. supplementary data, in the form of a range that shall describe additional "out of office" days that the spreadsheet can not know about in other form. Dates that are holidays but happen to be regular weekend days are not counted. The first 2 parameters are compulsory, the 3rd one is optional. The situation of the =WORKDAY function is quite the same, only the 2nd parameter is not a date, but a number of days to step over for getting the final date. Looking for the number of Saturdays and Sundays we find a total of 9 ( 4 Sundays and 5 Saturdays). In Israel we do not work on Fridays and Saturdays and summing up this count for April 2005 we arrive at a total of 10. It is very possible that other parts of the Middle East have other rules. I think that Gnumeric is a very good-looking piece of programming work and the intention to be used all over the world should be sustained by practical endeavour.
I see no reason that the NETWORKDAYS function should be locale specific. It always skips Saturdays and Sundays, i.e., it may do something other than what you need. (Should DATE(1910,1,1) return something else in Russia?) What does Excel do? If Excel does it, we might have to do it also for compatility reasons, but if not the solution is more like coming up with a new function that does what you need. Having hidden arguments to functions is a nightmare testing-wise.
Morten summarizes the question and solution here nicely. 1) We need to be MS Excel compatible for this specific function. If they adjust behavior based on locale we will need to do so also. 2) It would be trivial to add a new function with the added features of supporting different work environments. Propose an interface on the mailing list and we can discuss it.
In my opinion, compatibility should be a two-direction issue. Introducing a new function to do the work would drastically reduce one of the directions. Anyhow, Gnumeric presentation sustains that there are some 60 functions not found in Excel, so the problem of compatibility is already solved. Returning to the definitions of the 2 functions, I did not succeed finding any reference of "...always skips Saturdays and Sundays..."; there is nothing like this, nowhere. The absence of such a statement gives me the liberty to think that the function should return a result appropriate to the area I live in. A spreadsheet for computing hourly salaries may be written anywhere in the world and it should yield the correct results at any place of its usage, without structural modifications. For the moment, neither Gnumeric nor Excel can do that.
I don't think anybody argues that you should't be able to write such a spreadsheet. In fact with some effort you can probably do that now. Unfortunately, gnumeric functions with the same name as xl functions are expected to behave the same as the xl functions. (I have argued against that for quite a while and am slowly consding the point.) Since you seem to say that xl does not provide the appropriate function, Jodys point #2 is surel the appropriate solution. So you may want to propose an interface.
> A spreadsheet for computing hourly salaries may be written anywhere in the > world and it should yield the correct results at any place of its usage, > without structural modifications. For the moment, neither Gnumeric nor Excel > can do that. That looks more like an argument for being locale-INdependent. Suppose we made NETWORKDAYS locale dependent. You could then make a sheet and plug in some data. It might say salary $4321. Send it off to someone else, and it might say salary $3210. Nobody is going to be happy that way. I don't believe the ability to share templates (==sheet with formulas, but no data) is worth that pain. The question still stands: what does Excel do? I do not have a suitable locale to check that myself. Once we know that, we can either... 1. If Excel does things locale dependent, do the same. ...or... 2a. Improve the documentation to state that as far as NETWORKDAYS is concerned, weekends are Saturdays and Sundays. 2b. Introduce a new function, probably with the weekend-rule as an explicit parameter.
By making a function locale-dependent, I understand that in your locale, you'll have to work 21 days in April 2005 and in my locale I'll have to work 20 days in the same period. Excel does exactly the same thing as Gnumeric. There is no problem to change the locale on a Windows XP machine, only use "Date,Time,Language and Regional Options" from Control Panel. The program is yours and you'll decide what you want to do. I wanted only to help. For the moment I don't think that adding a function is the best move to take, so I won't propose any interface.
Ok, thanks. We'll try to come up with a solution that doesn't cause more trouble that it fixes. Re control panel: I am only offered a few options in that position so while I can change things, I cannot change it to, say, Israeli standards. I imagine it might be a matter of what has been chosen during installation.
ODF has added a fourth optional parameter that specifies which days are "weekend". I guess we should go that way. As long as the user doesn't use the fourth parameter, it stays XL compatible.
A optional weekend specifier compatible with OpenFormula has been added to WORKDAY.
This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.