Hello Excellers welcome to another #formulafriday. I want to share with a handy formula today that will allow you to find the fourth business day of the next month. This is a great formula especially if you run regular reports that need to be completed on a certain business day following a month end.
A lot of businesses have a month end or period end process which has to complete before all transactions are posted to systems and ready to analyse. This formula will give you the exact date that you need to run your reports if they are to be carried out on a certain specified day of the following month.
The formula uses both the WORKDAY and the EOMONTH functions-
What Does The WORKDAY function do?
The WORKDAY function return ( in the form of a serial number) the date before or after a specified number of workdays.
The syntax of WORKDAY is
Start_date this represents the start date
Days this is the number of non weekend and non holidays before or after the specified Start_date
Holidays this is an optional array of one or more dates to exclude from the working calendar, for example official holidays or any days you want to be treated as non working days
What Does The EOMONTH function do?
The EOMONTH function returns the serial number of the last day of the month, that is the indicated number of months before of after the Start_date
The syntax of EOMONTH is
Start_date – this is a required argument and represents the starting date. This needs to be entered in the date format
Months – this argument is also required, which represents the number of months before or after a start date, it can be either a positive number (a future date) or a negative number (a past date).
By combining these two functions we can work out the date we need to run our report, four working days into the next calendar month after the previous month end.
The Workday Start_date argument is supplied by the EOMONTH calculation of the value in B2 plus one month, which returns February 2017 and the days argument is 4 working days after this Start_date. We have no specified any other days as holidays – to be treated as non working days ( this is optional).
All we need to do is fill down the months of the year with the formula to calculate the due report dates for the rest of the year.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.