When I tried to search online as I’d always done, I can’t find a straight answer or formula to this question. Feeling challenged, I decide to decompose what Excel can do, and see if I can contribute to the Excel community.
This is helpful for calculating deadlines, timesheets etc.
Before constructing a formula, let’s see what Excel offers that can help in this.
Networkdays – This function calculates the number of working days between 2 dates. For it to work with your country’s calendar, you need to maintain a list of public holidays.
Weekday – This function returns the day of the week (e.g. 1 for Sunday, 2 for Monday, etc)
Workday – This function which is the one I’ll be using together with the Date function, returns the date after number of working days from a specified date.
The logic of it is simple: the first working day of the month is 1 working day after the end of the prior month. (i.e. April 2011 ends on a Saturday, 1 working day after is 2 May 2011)
My Excel function to solve the above question will be:
=WORKDAY(DATE([current] Year,[current] Month, Day),1,[Public Holidays])
You key in 0 for Day to indicate last day of the prior month
[Public Holidays] is a list of dates of the holidays in your country maintained by yourself
Do drop in the comments if you find anything that is unclear and need extra clarifications
- How To Use Calculations in Excel (clickitwithmel.wordpress.com)
- Learning Excel for Accounting (thinkup.waldenu.edu)
- Excel Guide for SEO – Lessons for Aspiring Ninjas | Distilled (distilled.co.uk)