How many working days are there in a month? For many of us doing budgeting, the number of days matter for either income or costs.
For years, I have spent a few moments as part of our budget setting exercise to look through an annual calendar, count the days in each month, then take the holidays I can remember, and exclude them.
[wow_colorme]It turns out that Excel can do this for you.[/wow_colorme]
There’s a formula called “Networkdays.intl” Here’s how it works.
Firstly, the full syntax is NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]). This will tell you how many work days fall between the start and end dates, and you will notice there are options for weekends and holidays.
Secondly, there is another formula called “EOMONTH” that tells you the last day of a month. So you can put together a formula that brings these together to show the first day, and the last day of the month.
The last piece is to create an excel range that shows the public (bank) holidays during the year. In this case, I have created a range that is just the column of dates below, and I called that range “holidays”. (cells F6 through to F38 in the example below)
Finally, we jut need to link it together. In this example, the cell C7 contains a date of the 1st of the month in question, and the formula “=NETWORKDAYS.INTL(C7,EOMONTH(C7,0),,Holidays)” tells you how many days are in the month.
Finally – here’s a handy source of the official holidays in New Zealand : https://www.employment.govt.nz/leave-and-holidays/public-holidays/public-holidays-and-anniversary-dates/