Show the actual Networkdays from 2 Date Values

[anthonyexcel]

I have a lot of data formated like below. Is there a way to show the dates from 2 values must be Networkdays. Please see below. Thanks!



******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********><CENTER><TABLE< a>cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="13" bgColor="#0c266b"><TABLE border=0 width="100%" align=center><TBODY><TR><TD align=left>Microsoft Excel - Book1</TD><TD style="FONT-FAMILY: caption; COLOR: #ffffff; FONT-SIZE: 9pt" align=right>___Running: 14.0 : OS = Windows XP </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px; BORDER-RIGHT: #000000 0.5pt solid" colSpan="13" bgColor="#d4d0c8"><TABLE border=0 width="100%" align=center VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-FAMILY: caption; COLOR: #000000; FONT-SIZE: 10pt">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp [URL="http://www.mrexcel.com/forum/#javascript<b></b>:void(0)"](A)bout</TD><TD vAlign=center align=right><FORM name=formCb755237><INPUT value="Copy Formula" type=button name=btCb873980 *******='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);'></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="13" bgColor="white"><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" bgColor=white align=middle><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION selected value=40422>A1</OPTION></SELECT></TD><TD bgColor=#d4d0c8 width="3%" align=right>=</TD><TD bgColor=white align=left><INPUT value="Absence Start Date" size=80 name=txbFb426622></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle" width="2%">
</TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>A</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>B</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>C</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>D</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>E</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>F</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>G</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>H</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>I</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>J</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>K</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>L</CENTER></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-BOTTOM: #000000 0.5pt solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid">Absence Start Date</TD><TD style="BORDER-BOTTOM: #000000 0.5pt solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid">Absence End Date</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid"></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-BOTTOM: #000000 0.5pt solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">8/23/2010</TD><TD style="BORDER-BOTTOM: #000000 0.5pt solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">9/1/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">8/23/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">8/24/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">8/25/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">8/26/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">8/27/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">8/28/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">8/30/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">8/31/2010</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Cambria; COLOR: #000000; FONT-SIZE: 9pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">9/1/2010</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="13"><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; WIDTH: 120pt; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=left>Sheet1</TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
[/URL]

 

[dannac]

=NETWORKDAYS(A2,B2)

works in 2003 if you have analysis tool pak installed

 

[anthonyexcel]

Thanks for the reply dannac but
I do not want to count the Networkdays. I want to show them. So if my start date was 8/23/2010 and my end date was 9/1/2010 then I would like the row to show

<TABLE style="WIDTH: 252pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=333><COLGROUP><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" span=9 width=37><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 28pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=16 width=37>8/23</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>8/24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>8/25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>8/26</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>8/27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>8/28</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>8/30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>8/31</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>9/1</TD></TR></TBODY></TABLE>

 

[dannac]

ahhh... yes, you stated that in your first post, my mistake.

Someone with more know-how may come along.

 

[AlphaFrog]

I think you would have to do this with a macro.

Is this only for the dates in cells A2:B2 ?
Or will you have multiple date pairs in columns A:B ?

 

[anthonyexcel]

Thanks for the responses! AlphaFrog,I will have multiple pairs in A:B

 

[barry houdini]

Try this formula in D2

=IF(WORKDAY($A2-1,COLUMNS($D2:D2))>$B2,"",WORKDAY($A2-1,COLUMNS($D2:D2)))

format in required date format and copy across as far as might be required....and down for other dates

 

[anthonyexcel]

Thanks Barry! Is there any way to get Network Days involved because I have some holidays that I want to exclude! Thanks

 

[T. Valko]

Thanks Barry! Is there any way to get Network Days involved because I have some holidays that I want to exclude! Thanks

The WORKDAY function will also accept a holidays argument just like NETWORKDAYS.

 

[anthonyexcel]

OK Biff Thanks! Thanks again Barry for all of the help!