Next working day

In this tutorial, you will learn how to get a next working day based on a chosen date. This is possible by using Excel WORKDAY function. Also, you will get familiar with two different ways of next working day calculation. The first one excludes only weekends, while the second one excludes defined holidays’ dates beside weekends. Above all, the prerequisite for WORKDAY function usage is the formatting of all fields to date data type.

Next working day

Formula

=WORKDAY(start_date, days, [holidays])

Explanation

First of all, let’s see the parameters of Excel WORKDAY function:

Start_date is a date for which we want to get the next working day. Days is a number of working days that we want to add to a given start date. We want to get the next working day, so 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 therefore enables the function to take in counts holiday days beside weekends. 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.

Example 1

In the first example, we will see how to find the next working day for a list of dates, without taking holidays into account. Let’s see how the formula looks like:

=WORKDAY(B3,1)

Figure 1. Next working day without holidays

As you can see in the picture, we want to find the next working day for cell B3 and put it in the cell C3. Therefore, our start date will be 24-Feb-18. As this is Saturday, the formula will skip the next day (Sunday) and return 26-Feb-18 (Monday), which is the next working day.

Example 2

Similarly to the first example, we will see how 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:

=WORKDAY(B3,1,public_holidays)

Figure 2. Next working day with holidays

The only difference between this and the first example is filling the parameter Holidays with named range “public_holidays”. Therefore, the next working day of 30-Dec-17 will be 2-Jan-18. January 1 was not taken into account, although it is Monday, because we put it in our table with holidays.