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

jazzysweet

New Member
Joined
May 5, 2009
Messages
2
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,899
Office Version
365, 2010
Platform
Windows
NETWORKDAYS only counts weekdays, and includes an optional parameter for a range to hold holidays to exclude

Excel Workbook
ABC
15/1/20095/8/20096
Sheet3
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
May 5, 2009
Messages
2
Richard and Hotpepper, thank you soo much!

I used Richard's formula and it worked perfectly!
 

amarpreet

New Member
Joined
Feb 11, 2013
Messages
1
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.
 

Forum statistics

Threads
1,085,565
Messages
5,384,474
Members
401,903
Latest member
MarBer

Some videos you may like

This Week's Hot Topics

Top