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.
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.
You now have the third business day of each month.
Find out more about our Microsoft Excel training London, UK.