Formula Friday – Know Your Dates!!. Calculate The Exact Dates Reports Are Due Using WORKDAYS and EOMONTH Function..


Hello and welcome back for some more #formulafriday formula fun.  Today let’s look at how to calculate the exact dates that your excel reports are due.  We can do this with two formulas.  The WORKDAY Function and the EOMNTH Function. 

A lot of businesses have a month end or period end process.  Staff usually have to complete a specific month end process before all transactions are posted to systems and ready to analyse.  This excel formula combination will give you the exact date that you need to run your reports.  This is really useful if they are to be carried out on a certain specified day of the following month.

The Formulas.

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.  The user needs to specify 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 or after the Start_date
The syntax of EOMONTH is

Start_date –  this is a required argument and represents the starting date.   We need to enter 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.

WORKDAY AND EOMONTH FUNCTION

Our Workday Start_date argument is supplied by the EOMONTH calculation of the value in B2 plus one month, which correctly returns February 2017 and the day’s argument is 4 working days after this Start_date.  In this example, We have not specified any other days as holidays.  When a user specifies this argument,  then the days are to be treated as non-working days.  It’s an optional argument for the user.

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.
 Do you find this useful or do you have another method of calculating the dates your Excel reports are due?.  Why not leave a comment in the sections below to share with other blog readers?.

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

1

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.

More Excel Tips_New1
Learn Excel With Our Excel Online Courses

excel-formula-crash-course-join-today-v1

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