Calculating Working Days with a twist

[JEB85]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I’m trying to calculate the number of working days between two dates. The NETWORKDAYS function isn’t any good because it includes the begin and end date in the calculation.<o></o>
<o> </o>
Below is an example, and column C are the values that I’m after.<o></o>
<o> </o>
<TABLE style="MARGIN: auto auto auto -0.75pt; WIDTH: 225pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=300><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=64 noWrap></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=75 noWrap>

A<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 54pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=72 noWrap>

B<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 67pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=89 noWrap>

C<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=64 noWrap>

1<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=75 noWrap>

Compliance<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 54pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=72 noWrap>

Completion<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 67pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=89 noWrap>

Working Days<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=64 noWrap>

2<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=75 noWrap>

15/04/2011<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 54pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=72 noWrap>

22/04/2011<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 67pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=89 noWrap>

5<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=64 noWrap>

3<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=75 noWrap>

15/04/2011<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 54pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=72 noWrap>

26/04/2011<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 67pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=89 noWrap>

7<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=64 noWrap>

4<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=75 noWrap>

25/04/2011<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 54pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=72 noWrap>

20/04/2011<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 67pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=89 noWrap>

3<o></o>​

</TD></TR></TBODY></TABLE>
<o> </o>
Can anyone assist?<o></o>
<o> </o>
Thanks<o></o>

 

[gsistek]

Are your Cells A4 and B4 correct or are they supposed to be switched?

 

[Zakkaroo]

I don't really know what the NETWORKDAYS function does .... but if it includes the begin and end date ... then surely the result will always be +2 above your desired result. So couldn't you just put -2 at the end of it?

 

[gsistek]

Using -1 at the end of the formula gives the desired result, except in the row4 case. Either the data is backward or you need to put =abs( ) around the entire formula.

 

[Jonmo1]

It seems you only want to exclude the start date, but INclude the end date.
Because in your first example, it's friday to friday.
Networkdays normally would result in 6, but you want 5.


Try just adding 1 to the start date

=Networkdays(startdate+1,enddate)


or if it's the end date you want excluded, subtract 1 from it.

=Networkdays(startdate, enddate-1)


hope that helps.

 

[JEB85]

Thanks very much for the responses.

gsistek - column a or column b can have the bigger date value

Jonmo1 - I've adapted what you've suggested and it's working great so thanks a lot.

Here's the formula I've used in C2;

=IF(SUM(A2-B2)=0,0,IF(MAX(A2,B2)=B2,NETWORKDAYS(A2+1,B2),NETWORKDAYS(A2-1,B2)))

 

[gsistek]

If you want to shorten the formula at all, I would suggest =ABS(NETWORKDAYS(A2,B2))-1

 

[JEB85]

I'm all for efficiencies gsistek, thanks!