Modify the official None-working days in the "NETWORKDAYS" Functio

[Guest]

I have a problem working with this function:

NETWORKDAYS(start_date,end_date,holidays)

This function calculates the number of "Working Dates" between any two given
dates. My problem is that this function calculates Saturday and Sunday as
none-working days, but in the country I’m working in, we have Friday and
Saturday as our none-working days. Therefore, it gives an inconsistent
answer. Do you have any idea if can I make the function start calculating
Friday and Saturday as the official none-working days?!

 

[Bob Phillips]

Zewer,

Try this

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1
-{1;2;3;4;5})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{1;2;3;4;5},0))*(holidays>=MIN(
end_date,start_date))*(holidays<=MAX(end_date,start_date)))

 

[Aladin Akyurek]

See Larson's contrib in: http://tinyurl.com/czdzl

 

[Ron Rosenfeld]

I have a problem working with this function:

NETWORKDAYS(start_date,end_date,holidays)

This function calculates the number of "Working Dates" between any two given
dates. My problem is that this function calculates Saturday and Sunday as
none-working days, but in the country I’m working in, we have Friday and
Saturday as our none-working days. Therefore, it gives an inconsistent
answer. Do you have any idea if can I make the function start calculating
Friday and Saturday as the official none-working days?!

Probably the simplest method is to add 1 to all of your dates within the
NetWorkdays function.

=NETWORKDAYS(start_date+1,end_date+1,holidays+1)

However, if you use the optional holidays parameter, I believe you must enter
the formula as an **array** formula and that it will work OK that way. To
enter as an *array* formula, after typing the formula into the formula bar,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces {...}
around the formula.


--ron