NETWORKDAYS function: How to include Saturdays as workdays?

[taurean]

Hi, I have never worked on workdays before so I do not know most of the available formula and play around. So I am taking this shortcut of asking you guys. I googled across and found this one:
http://www.excelbanter.com/showthread.php?t=91820
Where Bob has given an ARRAY formula.

Following is my layout in A1 to C3 grid (for testing purpose):
<TABLE style="WIDTH: 215pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=286><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 88pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=117>Start Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=83>11/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=86>HOLIDAY</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 88pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=117>End Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=83>11/30/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=86>11/3/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 88pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=117>NETWORKDAYS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=83>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=86>11/22/2011</TD></TR></TBODY></TABLE>
The formula resides in B3.

Is there any other development in 2007+ versions of Excel which can handle 6 working days in a week?

 

[taurean]

You are right, the formula does give incorrect result. Besides, it needs to be CSE-d which is not the case with your formula.

I will briefly put it in words:

1. An Array shall be generated which will include the days starting from B1[Start Date] to B2 [End Date].

2. Then each date shall be evaluated based on WEEKDATE. Unmatching items will return false (0) and matching items would generate 1 which would be used for summing up.

3. The difficulty was generating an exact sized array (B2-B1+1) mentioned in step 1 which I did using IF condition but on the other side ROW($C$1:$C$365) would generate 1,2,3 so to counter that I subtracted 1 from it so it would be 0,1,2,3 for corresponding 1, 2, 3, 4 etc.

So it finally became this (after correcting)

{=SUM(IF(ROW($C$1:$C$365)<=(B2-B1+1),IF(WEEKDAY(ROW($C$1:$C$365)+B1-1)<>1,1)))}

This time around I have tested it vis-a-vis your formula and results were matching.

Thank you for replying because without your response I wouldn't have checked into it. And it will take some time for me to get my hands on 2010. But it is better to have a standard built-in function.

 

[mahmed1]

I believe that only works coincidentally in your example, ROW(C1:C365)+(B2-B1) doesn't give the correct dates so using WEEKDAY on that can't guarantee the correct result.

Try October 2011, for instance, your formula returns 27 when the correct result is 26. As Biff says, in Excel 2010 you can get that result with NETWORKDAYS.INTL, i.e.

=NETWORKDAYS.INTL(B1,B2,11)

or in any version

=B2-B1-INT((1-WEEKDAY(B2)+B2-B1)/7)

Hi Barry does the second formula take into account any Bank holidays?

My working day is Monday to Saturday?

Thank You

 

[barry houdini]

Try Biff's suggestion to exclude Sundays and holidays, where start date is B1, end date B2 and holidays in C2:C3

Here's another formula.

=SUM(INT((WEEKDAY(B1-{2,3,4,5,6,7})+B2-B1)/7))-SUMPRODUCT(--(C2:C3>=B1),--(C2:C3<=B2))

 

[mahmed1]

Try Biff's suggestion to exclude Sundays and holidays, where start date is B1, end date B2 and holidays in C2:C3

Thank you,

Is there any chance you or Biff can explain how the formula is working?

Thank you

 

[T. Valko]

Thank you,

Is there any chance you or Biff can explain how the formula is working?

Thank you

Barry explains how that first part of the formula works here:

http://www.mrexcel.com/forum/showthread.php?t=542039

The SUMPRODUCT is counting how many holiday dates fall within the date range.

 

[pgc01]

Hi

We can add

=SUM(INT((WEEKDAY(B1-{2,3,4,5,6,7})+B2-B1)/7))-SUMPRODUCT(--(C2:C3>=B1),--(C2:C3<=B2),--(WEEKDAY(C2:C3)<>1))

to account for holidays that follow on a Sunday

 

[pgc01]

to account for holidays that follow on a Sunday

I meant:

to account for holidays that fall on a Sunday