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?

 

[pgc01]

Hi Shrivallabha

There is in fact (finally) a Workday.Intl() function to deal with other weekday definition than the Mon-Fri in excel 2010.

Check:

http://office.microsoft.com/en-us/excel-help/workday-intl-function-HA010354380.aspx

 

[T. Valko]

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" cellSpacing=0 cellPadding=0 width=286 border=0><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 class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #d8d8d8" width=117 height=20>Start Date</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=83>11/1/2011</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b8cce4" width=86>HOLIDAY</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #d8d8d8" width=117 height=20>End Date</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=83>11/30/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>11/3/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #d8d8d8" width=117 height=20>NETWORKDAYS</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=83>20</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" 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?

Shouldn't the correct result be 24?

Here's another formula.

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

 

[taurean]

Hi Shrivallabha

There is in fact (finally) a Workday.Intl() function to deal with other weekday definition than the Mon-Fri in excel 2010.

Check:

http://office.microsoft.com/en-us/excel-help/workday-intl-function-HA010354380.aspx

Sorry for late reply. The construct is little different in the sense that it doesn't ask you for the END DATE. I suppose, we can calculate the days in this
WORKDAY.INTL(start_date, days, [weekend], [holidays]) by using simple math subtraction like A1 - B1. I don't have 2010 (office / home) but will take a look for sure when I do.

Shouldn't the correct result be 24?

Here's another formula.

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

Your result is absolutely correct. Sorry for the insufficient info.

It was my mistake. I should have written the current formula in the post which was existing NETWORKDAYS formula and the desired output (which your formula gives). Can you please help me understand the logic? I understand the SUMPRODUCT part no problem.

 

[pgc01]

Sorry for late reply. The construct is little different in the sense that it doesn't ask you for the END DATE.

Sorry, I thought you knew that there are 2 functions to work with the workdays. You usually work with both to deal with these kind of problems.

In excel 2010 you have international versions for both:

Before excel 2010-> After excel 2010
Workdays() -> Workdays.Intl()
NetWorkdays()-> NetWorkdays.Intl()

Check here:

http://office.microsoft.com/en-us/excel-help/networkdays-intl-function-HA010354379.aspx

 

[T. Valko]

Your result is absolutely correct. Sorry for the insufficient info.

It was my mistake. I should have written the current formula in the post which was existing NETWORKDAYS formula and the desired output (which your formula gives). Can you please help me understand the logic? I understand the SUMPRODUCT part no problem.

See this:

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

 

[taurean]

Sorry, I thought you knew that there are 2 functions to work with the workdays. You usually work with both to deal with these kind of problems.

In excel 2010 you have international versions for both:

Before excel 2010-> After excel 2010
Workdays() -> Workdays.Intl()
NetWorkdays()-> NetWorkdays.Intl()

Check here:

http://office.microsoft.com/en-us/excel-help/networkdays-intl-function-HA010354379.aspx

I should admit that I wasn't reading carefully. I do not normally have to use these so I have little or limited idea about these functions. I was curious so I asked. Thank you so much guys. I really appreciate your help on this.

Biff, I have added your link to my collection of useful URLs [function related]. Your formula crunching ability is stupendous!

 

[T. Valko]

I should admit that I wasn't reading carefully. I do not normally have to use these so I have little or limited idea about these functions. I was curious so I asked. Thank you so much guys. I really appreciate your help on this.

Biff, I have added your link to my collection of useful URLs [function related]. Your formula crunching ability is stupendous!

You're welcome. Thanks for the feedback!

 

[taurean]

This afternoon, I was trying to remember the formula in the office but the logic or the formula both deserted me. So thought about different approach, and it works.

Please note I have concentrated only on NETWORKDAYS part so the result is 26:

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 110px"> <col style="WIDTH: 83px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="HEIGHT: 19px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="BACKGROUND-COLOR: #ccccff; FONT-SIZE: 12pt">Start Date</td> <td style="TEXT-ALIGN: right; FONT-SIZE: 12pt">11/1/2011</td></tr> <tr style="HEIGHT: 19px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="BACKGROUND-COLOR: #ccccff; FONT-SIZE: 12pt">End Date</td> <td style="TEXT-ALIGN: right; FONT-SIZE: 12pt">11/30/2011</td></tr> <tr style="HEIGHT: 19px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="BACKGROUND-COLOR: #ccccff; FONT-SIZE: 12pt">NETWORKDAYS</td> <td style="TEXT-ALIGN: right; FONT-SIZE: 12pt">26</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B3</td> <td>{=SUM(IF(ROW(C1:C365)<=(B2-B1+1),IF(WEEKDAY(ROW(C1:C365)+(B2-B1))<>1,1)))}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
Excel tables to the web >> Excel Jeanie HTML 4

 

[barry houdini]

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)