NETWORKDAYS function: How to include Saturdays as workdays?

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

Biff said:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881

ADVERTISEMENT

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
 

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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! :cool:
 

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,669
Messages
5,597,474
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top