Excel Formula to count weekdays in a date range while excluding holidays

[jazzysweet]

Hello,

I have been using the following function to count the number of weekdays in a given range( Example for Mondays):

= SUMPRODUCT( --(WEEKDAY( ROW( INDIRECT( DATE($B$5,$C$5,$D$5) & ":" & DATE($B$6,$C$6,$D$6) ) ) ) = 2 ) )

I now need to be able to exclude holidays from this result. I noticed the NET WORKDAYS function but I don't know how to get it to work to only count for weekdays like my sumproduct function does.

I would attach the file but don't see an attachment option.

Any suggestions are greatly appreciated.

 

[Scott Huish]

NETWORKDAYS only counts weekdays, and includes an optional parameter for a range to hold holidays to exclude

Excel Workbook
	A	B	C
1	5/1/2009	5/8/2009	6
Sheet3

 

[Richard Schollar]

Hi Jazzysweet

Looks to me like you don't want to count all weekdays, but just specified weekdays. You could use the following:

<table style="border: 1.5px solid rgb(166, 170, 182); font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 0.25em; padding-right: 0.5em; border-collapse: collapse; color: rgb(38, 34, 48);" border="1" cellpadding="0" cellspacing="0"><colgroup><col style="background-color: rgb(224, 224, 240);"><col><col></colgroup><tbody><tr style="border: 1px solid rgb(166, 170, 182); background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><td style="">
</td><td>A</td><td>B</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">1</td><td style="font-weight: bold; color: rgb(0, 0, 0);">Start Date</td><td style="font-weight: bold; color: rgb(0, 0, 0);">End Date</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">2</td><td style="color: rgb(0, 0, 0);">01 January 2009</td><td style="color: rgb(0, 0, 0);">31 May 2009</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">3</td><td style="color: rgb(0, 0, 0);">
</td><td style="color: rgb(0, 0, 0);">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">4</td><td style="font-weight: bold; color: rgb(0, 0, 0);">Holidays</td><td style="color: rgb(0, 0, 0);">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">5</td><td style="color: rgb(0, 0, 0);">13/04/2009</td><td style="color: rgb(0, 0, 0);">=Monday</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">6</td><td style="color: rgb(0, 0, 0);">09/04/2009</td><td style="color: rgb(0, 0, 0);">=Friday</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">7</td><td style="color: rgb(0, 0, 0);">04/05/2009</td><td style="color: rgb(0, 0, 0);">=Monday</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">8</td><td style="color: rgb(0, 0, 0);">
</td><td style="color: rgb(0, 0, 0);">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">9</td><td style="font-weight: bold; color: rgb(0, 0, 0);">Number of Mondays</td><td style="color: rgb(0, 0, 0);">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">10</td><td style="color: rgb(0, 0, 0);">21</td><td style="color: rgb(0, 0, 0);">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">11</td><td style="font-weight: bold; color: rgb(0, 0, 0);">Number of Mondays (Excluding Holidays)</td><td style="color: rgb(0, 0, 0);">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32);">12</td><td style="color: rgb(0, 0, 0);">19</td><td style="color: rgb(0, 0, 0);">
</td></tr></tbody></table>
</br>
<table style="border: 1.5px solid rgb(166, 170, 182); padding: 0.5em; background-color: rgb(255, 255, 255); border-collapse: collapse;" border="1" cellpadding="0" cellspacing="0"><tbody><tr><td style="font-weight: bold;">A10</td><td>=INT((WEEKDAY(A2-2)-A2+B2)/7)</td></tr><tr><td style="font-weight: bold;">A12</td><td>=INT((WEEKDAY(A2-2)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=2),--(A5:A7>=A2),--(A5:A7<=B2))</td></tr></tbody></table>

 

[jazzysweet]

Richard and Hotpepper, thank you soo much!

I used Richard's formula and it worked perfectly!

 

[amarpreet]

Hi ,

very sorry to open up this old thread , but i have a problem.

i used the function =(networkddays(A9,B9)-C9) for D9 .
now i want to use the number of days i get the in the D9 Field for dividing it by 21 . but this only always retuns a value of 1/10/1900.

my question is how do i use the value returned by networkdays() in d9 for furthur calculations.



Thanks
Amarpreet.