February 06, 2011

Networkdays Per Week Between 2 Dates

Gee, time flies when you are moping around not posting on your Excel blog. In 2009 I posted a blog entry called Days Per Week Between 2 Dates. At the end I noted that the formula did not take weekends or public holidays into account. Recently I made a formula that did.

Here it is in all of it's unwieldy glory. Note that Cell A2 is the Start Date, Cell B2 is the End Date and Cells in Row 1 refer to the first day in the weeks that you wish to calculate the workdays. The named range refers to a Dynamic List that contains public holidays. The formula can be entered in Cell C2 and dragged to the right to populate cells in the second row - D2, E2, F2 and so on.

=IF(OR(AND($A2<C$1,$B2<C$1),AND($A2>C$1+6,$B2>C$1+6)),"",IF(AND($A2>=C$1,$A2<C$1+6,$B2<=C$1+6),NETWORKDAYS($A2,$B2,Public_Holidays),IF(AND($A2<C$1,$B2>C$1+6),NETWORKDAYS(C$1,C$1+6,Public_Holidays),IF(AND($A2<C$1,$B2<=C$1+6),NETWORKDAYS(C$1,$B2,Public_Holidays),NETWORKDAYS($A2,C$1+6,Public_Holidays)))))


And a pic of it in action. Note that 26 January is a holiday - Australia Day - and it's on a Wednesday. So the week starting on 22 January only has 4 working days instead of the usual 5. (I conveniently ignored the fact that 1 January is New Year's Day though!)



Now, I just know that somebody is going to dazzle me with a much shorter version (or at least, I hope they are). Have at it ladies and gentlemen! I await your formula to add at the bottom of this blog post. Good luck ;-)