Formula Friday – Find The Date Of The Fourth Business Day Of The Next Month.


Do you find yourself trying to figure out the date of the a specific business day of a future month? If so, you’re not alone. It’s a question that can stump even the most experienced Excel users – but don’t worry! We have all the information and tools you need to make this calculation as fast and easy as possible. In this blog post, I demonstrate how tu use Excel to determine what day will be the fourth business day f the next month. This formula can be used from any starting point on the calendar. So buckle up because by the end of this Tutorial Tuesday, you’ll be able to calculate dates with confidence!

Excel Macro

So, a lot of businesses have a month end or period end process.  This formula returns the exact date that reports need to run on that fourth business date of the 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.  Enter this in the date format

Months –  this argument is also required, which represents the number of months before or after a start date. This argument can be either a positive number (a future date) or a negative number (a past date).

These two formulas combined will return the date to run the month end reports. In today’s scenario it is four working days into the next calendar month after the previous month end.

day of month Excel

The Workday Start_date argument is supplied by the EOMONTH calculation of the value in B2 plus one month. This argument returns February 2017, and the days argument is 4 working days after this Start_date.  There are no specified days as holidays or non working days (this is an optional).

Drag the formula down the column to populate the dates for the full year reports due dates.

there you have it! The date of the fourth business day of next month. This was a fun little exercise and I hope you enjoyed it as much as I did.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips every month and receive my free Ebook, 50 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.

How To Excel At Excel – Macro Mondays Blog Posts.

More Excel Tips.

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts