In this tutorial, you will learn how to get a next business day 6 months in the future. To be able to calculate this day, you will have to get familiar with WORKDAY and EDATE functions. The WORKDAY function returns next business day, while EDATE function returns a date in a certain number of months. Also, you will see how to exclude only weekends or holidays beside weekends. Also, you will have to format all cells to date format.
Next business day 6 months in future
=WORKDAY(EDATE(date, months) - 1, days, [holidays])
Let’s first look at EDATE function. This function returns a date in a selected number of months. Parameters of the function are Date – a date for which we want to add a certain number of months. Months – a number of months that we want to add on a selected date.
The parameters of Excel WORKDAY function are:
Start_date is a date for which we want to get the next working day. In our case, the start_date will be the return of EDATE function subtracted by 1. Days is a number of working days that we want to add to a given start date. As we want to get the next working day, therefore we will always set this parameter to 1. By doing this, the function will always sum our start date with one working day. Holiday is a non-obligatory parameter which enables the function to take in counts holiday days beside weekends. Also, to enable this, we need to create a table with all holiday’s dates. Also, we need to define a name for range containing holidays’ dates.
Let’s first look at how to find the next business day 6 months in the future for a list of dates, without taking holidays into account. The formula looks like:
Figure 1. Next business day 6 months in future without holidays
In this example, we want to find the next business day 6 months in the future for cell B3 (30-Jun-17) and put it in the cell C3. Therefore, our start date will be the return of function EDATE(B3,6)-1 which is 29-Dec-17. As this is Friday, the next working day in 6 months is 01-Jan-18.
In the second example, we want to include holidays when counting working days. We created a new table which has the list of holidays and their dates and named the range F3:F6 “public_holidays”.
Here is the formula used for this example:
Figure 2. Next business day 6 months in future including holidays
As you can see, we created the formula similarly to the previous example, but now we included the parameter Holidays with named range “public_holidays”. Therefore, the next business day 6 months in the future from 30-Jun-17 will be 2-Jan-18, because January 1 was not considered, although it is Monday, because we put it in our table with holidays. As a result, the cell C3 will have value 2-Jan-18.