In planning and manhour forecasting you will repeatedly have to count the number of workdays within a certain date interval. In case the dates are all fridays or mondays this is easy of course, but if they are not, you cannot just count weeks and multiply by 5. Counting workdays gets even more complex if you want to account for public holidays as well.

Counting workdays by user defined function in VBA

In MS-Excel you can easily automate the process of counting workdays by using Visual Basic. And because it runs so fast, the algorithm can be quite ‘dumb’ : just run by each date in the interval, decide if it is a workday and if it is, count it:

VBA code for GetWorkDays() User Defined Function

So you’d feed this user defined function with two cells containing dates and it will return the number of days between them (start- and end-date included) that do not fall in the weekend (weekday numbers 6 & 7). If you test it you’ll find that this code runs so quick that it does not make sense to make it any smarter. Even with a time interval of 10 years, execution of this code will only take a fraction of a second!

Counting public holidays

Now what if we want to subtract public holidays? Well: we can make a list of those dates in a cell range and feed it to another function as input. Then all we need to do is run through this list and count the number of these dates that fall in the right timeframe while not being Saturdays or Sundays:

VBA-code for GetHolidays() User Defined Function

So now we can count the number of workdays and the number of holidays in a given timeframe. By subtracting the second number from the first we will get the available working days excluding any holidays. Note that you can also use the GetHolidays function if you have a list of planned vacation days for a team member or non-working days for a part-timer you have to plan into your project.

If you get the hang of it you can try combining the two functions into one, while making the Holidays parameter optional (so it’s not required to be specified but can be). You can also allow for multiple columns of holiday dates so you can account for public holidays and other types of non-workable days in one go.

The sky is the limit (as long as you got the hours to burn)!

 

Leave a Reply

Time limit is exhausted. Please reload the CAPTCHA.