changing weekends for NETWORKDAYS calculation

  • Thread starter Thread starter Bilal
  • Start date Start date
B

Bilal

Hi, I am using NETWORKINGDAYS function to calcuate the
working days out of 2 dates, by default the system choes
SAT and SUN and execlude them as weekends, but my weekends
are THU and FRI.

I would appreciate if somebody could help and tell me how
to reset my weekend as I want ! thanka
 
Hi
if your dates are in A1 and A2 try the following formula
=NETWORKDAYS(A1+2,A2+2,list_of_holidays)
 
=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MATCH(WE
EKDAY(ROW(INDIRECT(A2&":"&D2))),{5,6},0)))

where A2 < D2.

When A2 = Sun 23-May-04, D2 = Sun 13-Jun-04, and Holidays consists of:

Fri 28-May-04
Sat 29-May-04
Sun 30-May-04
Sat 5-Jun-04
Sun 6-Jun-04
Mon 7-Jun-04


the above formula gives a count of 11, while 14 by the formula Frank
suggested.
 
Hi aladin
good point: You're correct, my suggestion works only if no holiday list
is included!
 
Hi
and just an addition
if you use the formula
=NETWORKDAYS(A2+2,D2+2,Holidays+2)
and enter this as an array formula with CTRL+SHIFT+ENTER) this will
also return 11 as result. Will do some more testing with this but this
seems to be an alternative :-)
 
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon and Sun,
that is {1,7},

while the Holidays set is the same as in my previous post.


Frank Kabel said:
Hi aladin
good point: You're correct, my suggestion works only if no holiday list
is included!
Click to expand...
[...]
 
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon and Sun,
that is {1,7},

while the Holidays set is the same as in my previous post.

Frank Kabel said:
Hi
and just an addition
if you use the formula
=NETWORKDAYS(A2+2,D2+2,Holidays+2)
and enter this as an array formula with CTRL+SHIFT+ENTER) this will
also return 11 as result. Will do some more testing with this but this
seems to be an alternative :-)
Click to expand...
[...]
 
Hi Aladin
in this case (if I understood you correctly) I got the following
results:
1. Your formula: 1
2. My formula: 0

I think zero is the correct result in this case as both dates should
have been excluded.


--
Regards
Frank Kabel
Frankfurt, Germany


Aladin said:
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon
and Sun, that is {1,7},

while the Holidays set is the same as in my previous post.


Frank Kabel said:
Hi aladin
good point: You're correct, my suggestion works only if no holiday
list is included!

--
Regards
Frank Kabel
Frankfurt, Germany



=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MAT
CH(WE
Click to expand...
[...]
Click to expand...
 
Hi
see my other post. Nice discussion btw on a quite sunday :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Aladin said:
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon
and Sun, that is {1,7},

while the Holidays set is the same as in my previous post.

Frank Kabel said:
Hi
and just an addition
if you use the formula
=NETWORKDAYS(A2+2,D2+2,Holidays+2)
and enter this as an array formula with CTRL+SHIFT+ENTER) this will
also return 11 as result. Will do some more testing with this but
this seems to be an alternative :-)
Click to expand...
[...]
Click to expand...
 
Aladin,

In what way? I get zero as expected, and if I change D2 to Sat 29-May-2004,
I get 3.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Aladin Akyurek said:
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon and Sun,
that is {1,7},

while the Holidays set is the same as in my previous post.


Frank Kabel said:
Hi aladin
good point: You're correct, my suggestion works only if no holiday list
is included!
Click to expand...
[...]
Click to expand...
 
Hi
I think you meant to exclue {1,2} and not {1,7} in your formula. After
changing this both formulas return the same values

--
Regards
Frank Kabel
Frankfurt, Germany


Frank said:
Hi Aladin
in this case (if I understood you correctly) I got the following
results:
1. Your formula: 1
2. My formula: 0

I think zero is the correct result in this case as both dates should
have been excluded.



Aladin said:
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon
and Sun, that is {1,7},

while the Holidays set is the same as in my previous post.
Click to expand...
=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MAT
CH(WE
EKDAY(ROW(INDIRECT(A2&":"&D2))),{5,6},0)))

where A2 < D2.

When A2 = Sun 23-May-04, D2 = Sun 13-Jun-04, and Holidays consists
of:

Fri 28-May-04
Sat 29-May-04
Sun 30-May-04
Sat 5-Jun-04
Sun 6-Jun-04
Mon 7-Jun-04


the above formula gives a count of 11, while 14 by the formula
Frank suggested.
Click to expand...
[...]
Click to expand...
Click to expand...
 
Let me recap:

A2 = Sun 23-May-2004
D2 = Sun 23-May-2004

Weekdays to exclude: Mon and Sun [ {2,1}, by WEEKDAY(Date) ]
Holidays: The same set as before.

{=NETWORKDAYS(A2+2,D2+2,Holidays+2)} gives 1, while

=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MATCH(WE
EKDAY(ROW(INDIRECT(A2&":"&D2))),{2,1},0)))

calculates 0.

A2 = Sun 23-May-2004
D2 = Mon 24-May-2004

Weekdays to exclude and Holidays: Same as above.

{=NETWORKDAYS(A2+2,D2+2,Holidays+2)} gives 2, the SumProduct formula 0.

Make D2 Tue 25-May-2004, we get 3 vs 1.

Am I missing something here?

Frank Kabel said:
Hi Aladin
in this case (if I understood you correctly) I got the following
results:
1. Your formula: 1
2. My formula: 0

I think zero is the correct result in this case as both dates should
have been excluded.


--
Regards
Frank Kabel
Frankfurt, Germany


Aladin said:
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon
and Sun, that is {1,7},

while the Holidays set is the same as in my previous post.


Frank Kabel said:
Hi aladin
good point: You're correct, my suggestion works only if no holiday
list is included!

--
Regards
Frank Kabel
Frankfurt, Germany


Aladin Akyurek wrote:

=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MAT
CH(WE
EKDAY(ROW(INDIRECT(A2&":"&D2))),{5,6},0)))

where A2 < D2.

When A2 = Sun 23-May-04, D2 = Sun 13-Jun-04, and Holidays consists
of:

Fri 28-May-04
Sat 29-May-04
Sun 30-May-04
Sat 5-Jun-04
Sun 6-Jun-04
Mon 7-Jun-04


the above formula gives a count of 11, while 14 by the formula Frank
suggested.
Click to expand...
[...]
Click to expand...
Click to expand...
 
Hi aladin
of course I have adapted my formula to exclude Sundays + Mondays. In
this case I used:
{=NETWORKDAYS(A2+6,D2+6,Holidays+6)}
which also returns zero.
So with this change I also get the same results as your formula for the
other examples.

Generic formula would be
{=NETWORKDAYS(A2+(7-Start_day_to_exclude),D2+(7-Start_day_to_exclude),H
olidays+(7-Start_day_to_exclude))}

with Start_day_to_exclude defined as:
- Sunday=1
- Monday=2
-....
- Saturday=7


--
Regards
Frank Kabel
Frankfurt, Germany


Aladin said:
Let me recap:

A2 = Sun 23-May-2004
D2 = Sun 23-May-2004

Weekdays to exclude: Mon and Sun [ {2,1}, by WEEKDAY(Date) ]
Holidays: The same set as before.

{=NETWORKDAYS(A2+2,D2+2,Holidays+2)} gives 1, while

=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MAT
CH(WE
EKDAY(ROW(INDIRECT(A2&":"&D2))),{2,1},0)))

calculates 0.

A2 = Sun 23-May-2004
D2 = Mon 24-May-2004

Weekdays to exclude and Holidays: Same as above.

{=NETWORKDAYS(A2+2,D2+2,Holidays+2)} gives 2, the SumProduct formula
0.

Make D2 Tue 25-May-2004, we get 3 vs 1.

Am I missing something here?

Frank Kabel said:
Hi Aladin
in this case (if I understood you correctly) I got the following
results:
1. Your formula: 1
2. My formula: 0

I think zero is the correct result in this case as both dates should
have been excluded.


--
Regards
Frank Kabel
Frankfurt, Germany


Aladin said:
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon
and Sun, that is {1,7},

while the Holidays set is the same as in my previous post.


Hi aladin
good point: You're correct, my suggestion works only if no holiday
list is included!

--
Regards
Frank Kabel
Frankfurt, Germany


Aladin Akyurek wrote:
Click to expand...
=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MAT
CH(WE
EKDAY(ROW(INDIRECT(A2&":"&D2))),{5,6},0)))

where A2 < D2.

When A2 = Sun 23-May-04, D2 = Sun 13-Jun-04, and Holidays
consists of:

Fri 28-May-04
Sat 29-May-04
Sun 30-May-04
Sat 5-Jun-04
Sun 6-Jun-04
Mon 7-Jun-04


the above formula gives a count of 11, while 14 by the formula
Frank suggested.
[...]
Click to expand...
Click to expand...
Click to expand...
 
or

=NETWORKDAYS(A2-1,D2-1,Holidays-1)

as I used in my tests

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Frank Kabel said:
Hi aladin
of course I have adapted my formula to exclude Sundays + Mondays. In
this case I used:
{=NETWORKDAYS(A2+6,D2+6,Holidays+6)}
which also returns zero.
So with this change I also get the same results as your formula for the
other examples.

Generic formula would be
{=NETWORKDAYS(A2+(7-Start_day_to_exclude),D2+(7-Start_day_to_exclude),H
olidays+(7-Start_day_to_exclude))}

with Start_day_to_exclude defined as:
- Sunday=1
- Monday=2
-....
- Saturday=7


--
Regards
Frank Kabel
Frankfurt, Germany


Aladin said:
Let me recap:

A2 = Sun 23-May-2004
D2 = Sun 23-May-2004

Weekdays to exclude: Mon and Sun [ {2,1}, by WEEKDAY(Date) ]
Holidays: The same set as before.

{=NETWORKDAYS(A2+2,D2+2,Holidays+2)} gives 1, while

=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MAT
CH(WE
EKDAY(ROW(INDIRECT(A2&":"&D2))),{2,1},0)))

calculates 0.

A2 = Sun 23-May-2004
D2 = Mon 24-May-2004

Weekdays to exclude and Holidays: Same as above.

{=NETWORKDAYS(A2+2,D2+2,Holidays+2)} gives 2, the SumProduct formula
0.

Make D2 Tue 25-May-2004, we get 3 vs 1.

Am I missing something here?

Frank Kabel said:
Hi Aladin
in this case (if I understood you correctly) I got the following
results:
1. Your formula: 1
2. My formula: 0

I think zero is the correct result in this case as both dates should
have been excluded.


--
Regards
Frank Kabel
Frankfurt, Germany


Aladin Akyurek wrote:
Interesting, though it flounders on:

A2 = Sun 23-May-04, D2 = Mon 24-May-04, and days to exclude are Mon
and Sun, that is {1,7},

while the Holidays set is the same as in my previous post.


Hi aladin
good point: You're correct, my suggestion works only if no holiday
list is included!

--
Regards
Frank Kabel
Frankfurt, Germany


Aladin Akyurek wrote:


=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&D2)))=0),--ISNA(MAT
CH(WE
EKDAY(ROW(INDIRECT(A2&":"&D2))),{5,6},0)))

where A2 < D2.

When A2 = Sun 23-May-04, D2 = Sun 13-Jun-04, and Holidays
consists of:

Fri 28-May-04
Sat 29-May-04
Sun 30-May-04
Sat 5-Jun-04
Sun 6-Jun-04
Mon 7-Jun-04


the above formula gives a count of 11, while 14 by the formula
Frank suggested.
[...]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top