Excel WORKDAY Function – How to Use

The WORKDAY function is categorized as a Date and Time function. It helps us add or subtract N working days to a date provided by us.

The formula has various possible use-cases. For instance, think of a wholesale business that has agreed to deliver orders from retailers within 10 working days. Instead of keeping track of overdue orders manually, the wholesaler could use the WORKDAY function and quickly identify overdue orders.

Syntax

The syntax of the WORKDAY function is as follows:

Arguments:

'start_date' – This is a required argument where we enter either the date or a cell reference to a cell containing the date, from where we want our calculation to begin.

'days' – This is a required argument where we enter the number of working days we want to add to or subtract from the value in the start_date argument.

'holidays' – This is an optional argument where you may specify an array of dates that you want to exclude from workdays. The dates may be supplied either as a cell range or an array of serial numbers.

Important Characteristics of the WORKDAY function

• If the start_date and days arguments result in an invalid date, the function returns a #NUM error.
• If the value in the days argument is not an integer, the function will truncate the value.
• If the value(s) in the start_date or [holidays] argument is/are invalid, or the value in the days argument is non-numeric, the function returns a #VALUE error.

Examples of WORKDAY Function

Let's see some examples to understand the WORKDAY function in detail.

Example 1 – Plain Vanilla Formula for the WORKDAY Function

Let's start by calculating a working day 15 days from a given date. We won't add the holidays argument just yet. We'll use the following formula:

Here, we tried to calculate 15 working days in the future from December 25, 2001, and the function returned January 15, 2002. Notice how in the next row, all we do is change the value in the days argument negative to go 15 working days back starting from December 25, 2001. The return then, of course, changes to December 4, 2001.

Example 2 – Adding the holidays Argument to the Mix

In our previous example, the function returned January 15, 2002. Is that really 15 working days after the date we entered, though? Of course, there were some holidays during this period. For our next example, you'll need to create a list of holidays in December 2001 and January 2002 to account for these holidays in our calculation.

When you're done, use the following formula:

The return now changes to January 17, 2002, since there are two holidays (December 31 and January 1) in the period under consideration.

December 25, too, is a holiday. However, including our start_date in the list of holidays is moot. The formula starts its computation starting the next day, and the start_date is just used as a starting point.

Okay, that covers everything we need for take-off. Let's flex our muscles and fasten our seat belts and talk about more involved formulas.

Example 3 – Determine if a Date is a Workday

Let's say you want to determine if a date falls on a workday or not. Instead of scrolling through the calendar, you could just make use of the WORKDAY function to do this.

You can use the following formula to check if a date is a workday or a weekend/holiday:

To understand this formula, let's take a step back to see how the WORKDAY function works. The first thing we need to do is realize a fundamental attribute of the WORKDAY function – it never returns a weekend/holiday. You'll know in a minute why it's so important for this formula to work.

In the formula, we have first subtracted one day from the start_date and then added 1 working day via the days argument. This effectively circles back to the same date. Now, you might wonder why we couldn't have just used the following formula:

If you try using this formula, you'll realize that it doesn't work. The WORKDAY function doesn't calculate dates unless we provide an offset in the formula. So, what we're doing by subtracting and adding 1 day to the date is just elementary logic to accomplish one thing – get the WORKDAY function to fetch the same date that's in the start_date argument.

Next, we've entered a list of holidays. In our example, December 25, 2001, is the start_date as well as a holiday. Therefore, the WORKDAY function will return December 26, 2001, as its output.

Finally, we're equating this date with the date in cell A2 (i.e., the start_date). If both dates are equal, the final return will appear as TRUE, and FALSE otherwise. TRUE essentially tells us that the date returned by the WORKDAY function is the same as the input date, so the date is a workday, while a FALSE tells us that the WORKDAY function has fetched the next workday so the date must be a weekend.

Now, imagine if December 25, 2001, was a Sunday and assume that Christmas isn't added to the list of holidays. The formula would still return December 26, 2001 – because remember, it will never return a weekend/holiday. This is why it's an important attribute to understand.

WORKDAY vs. WORKDAY.INTL Function

WORKDAY.INTL is an extension of the WORKDAY function. The only difference is that the WORKDAY.INTL function allows us to add an extra argument to supply the custom weekends. Following is the syntax for the WORKDAY.INTL function:

Notice that the only difference here as compared to the syntax of the WORKDAY function is the presence of the [weekend] argument.

By default, both the WORKDAY and WORKDAY.INTL functions treat Saturdays and Sundays as the weekend. However, you can add a custom weekend in the WORKDAY.INTL function using the following predefined codes representing a weekend:

Let's use an example to see how the WORKDAY.INTL function works.

Say we run a fulfillment center that holds the inventory of a product and ships out the customer orders. Now, let's suppose our delivery fleet has decided on a two-day weekend and all Wednesdays and Thursdays will be off. In this case, we'd like to reschedule some of our orders, deliveries, and other trade agreements.

All you need to do then is to pull up a spreadsheet, enlist the dates when the orders were received, and input 30 in the days argument, assuming we take 30 working days for deliveries. Next, we'll use the following formula to calculate updated delivery dates based on the new weekend as implemented by the delivery fleet:

We've inserted 5 as the value in the [weekend] argument because that's the code that represents a Wednesday – Thursday weekend. Notice how some of the dates were rescheduled to the previous day while some were rescheduled to the next day.

WORKDAY.INTL Function Custom Weekend

Another noteworthy element of this formula is the code we use for the weekend argument. Excel's predefined codes should generally suffice. However, what happens if your delivery fleet decides that they want to have Mondays and Fridays off?

In that case, we use a text string in the weekend argument to tell the function which days are off vs. which days are working days. The text string must be a 7-character string where the first character represents a Monday and the last character represents a Sunday. In the string, we'll use a combination of 1s and 0s, where 1 represents a day off and 0 represents a working day.

The formula will look as follows, assuming Mondays and Fridays are off:

Our output now changes because we've again changed the definition of the weekend in the formula. Using the 7-character text string allows you to not only set two non-consecutive off days but also allows you to set 3 off days which isn't possible using Excel's predefined codes for the weekend argument.

That wraps up our WORKDAY function saga. Fiddle with these formulas and try to change them as you practice to see how it changes the output. When you've championed these formulas, we'll be back with another function to make your life a tad easier.