Help Wanted: Calculating the First Business Day of the Month

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,[0] 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

Advertisements

Tagged: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: