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.
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 😉