Microsoft Excel – third working day of every month

Someone asked me recently whether they could produce a series in Excel to find out the third working day of every month. To be honest, it may be as easy to do this manually as you still need to input the Bank Holiday dates into Excel to do the calculation but here goes!

Assuming you wanted to start with the third working day in 2016, somewhere in the spreadsheet, type Dec-2015 and in the next cell down type Jan-2016. Copy down for as many months as are needed; that series should fill in automatically.

BD1

Somewhere else in the spreadsheet type in all the Bank Holiday dates for 2016 (and beyond if required).

Then we need to make use of the EOMONTH function which gives the end of month date of the previous month so we will need to add three days to it to get the third day of the next month.

The syntax of the EOMONTH function is =EOMONTH(Start date, number of months). In this instance the number of months after the start date is 0 – we just want to add a few days to it.

We combine EOMONTH with the WORKDAY function. The syntax of the WORKDAY function is

=WORKDAY(Start date, days, holiday)

So combining the two, we have

=WORKDAY(EOMONTH(B1,0),3,$C$1:$C$8) where the EOMONTH function looks at the months in column B in turn, the 3 is for the third working day, and $C$1:$C$8 are our Bank Holiday dates and are absolute cell references.

Format the result as a date and copy the formula down.

BD2

You now have the third business day of each month.

Find out more about our Microsoft Excel training London, UK.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Uncategorized and tagged , , . Bookmark the permalink.

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