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.
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
What Does The EOMONTH function do?
Start_date – this is a required argument and represents the starting date. We need to enter the date format
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.