I came across this question on my RSS feed on Excel solutions from http://excel.tips.net/.
Strangely, when I clicked on it, I can’t find any answers.
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.
Date – This is the basic function for dates, that return a date where you key in a specific day, month and year.
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