WORKDAY Function

What is the WORKDAY Function?

The WORKDAY function lets you calculate the date 'n' working days in the future or past based on the input date.

In our professional career, there are many instances when we need to calculate dates in the future or the past. For example, one of the clients calls and gives us a deadline to work on a project that has been pending for a long time.

One of the most critical features of the function is that it only considers the weekdays and ignores Saturday and Sunday.

This lets us draw a clearer picture of what the deadline day for a task can be.

Another critical assumption with the function is that it lets us input holidays apart from Saturday and Sunday as an additional argument, thus streamlining the deadline schedule even further.

In this article, we will see what the WORKDAY function is, the syntax as well as how to use the function as well as a couple of examples.

Understanding the WORKDAY function

The WORKDAY is categorized as a Date and Time function that calculates date 'n' days in the future or past and returns the result as a serial number.

For example, assume that the date is 5th September 2022. First, you need to determine the date post 22 days. By using the formula, initially, we would get the result as 44839. This is because this serial number corresponds to the date 5th September 2022.

But wait! That's a difference of an entire month - a total of 30 days between the two dates. So didn't we only add 22 days to 5th September 2022?

The answer lies in the name of the function itself. What days do you usually work? The usual Monday to Friday as in most of the organizations throughout the world.

Similarly, the function only counts the working days while ignoring all the Saturdays and Sundays.

In our example, the dates that get skipped in calculating future dates are as below:

So that's a total of 8 days, while we had previously added 22 days to 5th September 2022, which is how we get the total of 30 days.

But you don't need to worry about the weekend dates as the function takes care of those for you.

WORKDAY function Formula

The syntax for the function is:

=WORKDAY(start_date, days, [holidays])

where,

• start_date - (required) the starting date
• Days - (required) number of days in the future or the past which will be added or subtracted, respectively
• holidays - (optional) a list of dates excluded from the workdays. For example, suppose you reference 5th September 2022 in the future/past date calculation as a holiday argument. In that case, the date will be ignored even if it is Monday.

Suppose you are determining a deadline for a project and a public holiday is imminent. In that case, you can use the holiday argument to highlight those dates and exclude them from future/past date calculations.

If you feel that you initially might make such mistakes, say input the date in DD-MM-YYYY format instead of MM-DD-YYYY, then we recommend using the DATE function.

The DATE function takes in three arguments - year, month, and date in the same order. So, for example, if you need to input the date as 5th September 2022, then the formula will be = DATE(2022,9,5) which should give you the expected result.

How to use the WORKDAY Function in Excel?

The function only considers three arguments, where one is an optional parameter. You can either select the function from the library or as a worksheet formula.

Method 1: From the function's library

The library is a great place to explore the functions if you are unaware of the different options available. For example, multiple functions might perform a similar task, such as IRR and XIRR but give contrasting results.

That's why if you are a beginner and don't know what different functions do, we advise you to check out all the tasks in the library.

To select the function from the library, please follow the steps below:

1. First, select the cell in which you intend to get the result.
2. Next, click on the Formulas tab > Date & Time > select the WORKDAY function from the drop-down menu.
3. A dialog box should open, as illustrated below:
4. As you can see, the dialog box describes everything, from arguments to what the function does. It even displays the preview of the result once you input the arguments.
   Here, you can directly reference the cells that contain the date values. If you hardcode the date in MM-DD-YYYY format, the function won't accept it.
   Another way is to input the date serial number, but that's quite a tedious task, so we will stick with referencing cells for the start_date argument.
5. We have the date as 6th September 2022 in cell B2 of our spreadsheet, which we will reference for start_date and hardcode the 'days' value as 22.
6. We get the result as serial number 44840, corresponding to 6th October 2022.

Method 2: As a worksheet formula

Using the function as a worksheet formula helps to save a lot of time. Still, the only requirement is to know what task different functions perform.

Suppose that you have the data in Excel, as illustrated below:

To use the function as a formula, we begin with an equal sign(=) in cell C5 and type in the function name followed by arguments in parentheses.

The formula will be

=WORKDAY(C2, C3)

giving us the result as 44839.

You can change the serial number into date format in several ways. Our preferred method is to select the cell and press the keyboard shortcut keys Alt + H + N + S, which changes the format of the number to 'short date.'

We will see a few examples to understand the function better.

WORKDAY Function Examples

The function does not have complicated syntax, and we quickly get the result in the selected cell. What more could you ask for?

In this section, we will see a couple of examples of the function. Remember that we still haven't focused on the holiday argument upon which we will base our first example.

Example 1

Suppose that today is 1st November 2022, and you have 30 days to complete a client project. You check the calendar and find that there are two public holidays, i.e., 11th November 2022 and 24th November 2022, as Veterans Day and Thanksgiving Day, respectively.

The data looks as illustrated below:

We have two values in the array of dates, i.e., 11th and 24th November. To calculate the deadline date, we will use the formula 

=WORKDAY(C2,C3,$E$3:$E$4) 

in cell C6, which gives us the result 44910.

By pressing Alt + H + N + S, we get the date as 15th December 2022.

What if we have not referenced the holidays in our formula? The result that we would get would be equal to 13th December 2022.

As you can see, the future date increases when you include the holiday's argument in the function.

Example 2

We had enough future date predictions based on the 'n' number of days. So, next, we will see an example to calculate a date 'n' days in the past.

Suppose that you order a product from an e-commerce website. The website did seem sketchy since they offered the product for free, but you just needed to pay $10 for shipping, so you try your luck.

Today is 6th September 2022, and it has been eight days since you started working on a client project. As per the calendar, you have a public holiday on the 1st and 2nd of September 2022.

On what day did you start working on the project?

The data looks as illustrated below:

Here, we will use the formula

=WORKDAY(C2,C3,$E$3:$E$4)

which gives us the starting date for the project as 23rd August 2022.

If there weren't any public holidays between those two days, the result would have been entirely different, i.e., 25th August 2022.

The WORKDAY function has various applications, such as calculating shipping dates, working days in an organization, etc.

WORKDAY vs. WORKDAY.INTL

Not every organization would have the same working days. For example, some organizations work Saturdays and prefer holidays on Sunday and Monday, while others might only have one holiday in the entire week, i.e., Sundays.

How do you decide the deadlines or the shipping dates then(in the case of an E-commerce company)?

It's none other than the WORKDAY.INTL function, which lets you select the weekend days in the function.

You can say it's a modified version of the WORKDAY function that lets you calculate a date 'n' days in the past or future based on the weekend you input. The different values for the weekend argument are as illustrated below:

The syntax for the function is:

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

where,

• start_date - (required) the starting date
• days - (required) number of days in the future or the past which will be added or subtracted, respectively
• Weekend - (optional) an additional argument that lets you decide what days will be considered weekend days and not working days. You can assign either two days or a single day as a weekend.

For example, number 3 assumes Monday and Tuesday as the non-working day, number 14 assumes Wednesday as the weekend, and the rest of the six days are work days.

Holidays - (optional) a list of dates excluded from the workdays.

Suppose that your organization has a five-day working week. However, as you have overseas clients, the company has declared Friday and Saturday non-working days.

On 1st September 2022, your clients ask you to deliver the project within 30 working days, with just a single public holiday on 15th September 2022.

The data looks as illustrated below:

By using the formula 

=WORKDAY.INTL(C3, C4,7, C6) 

in cell C8, we get the result as 44850.

Pressing the keyboard shortcut key of Alt + H + N + S and then Enter key, we get the date in the mm-dd-yyyy format as illustrated below:

Thus, the deadline for the clients' deliverables is 16th October 2022. Yes, we did not reference the weekend argument. Instead, Excel asked us to select the specific value from the drop-down for the corresponding weekend days.

If we had used the traditional WORKDAY function, the result would be:

We would get 14th October 2022 as our result, which falls on Friday, whereas our development uses WORKDAY.INTL falls on Sunday.

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• SUM Function
• STDEVPA Function
• TIME Function
• VAR.P Function
• YIELD Function