The WORKDAY function in Excel is a useful tool for calculating dates that are based on working days, excluding weekends and holidays. You can use the WORKDAY function to find out the due date of a project, the delivery date of an order, the completion date of a task, and so on.
In this article, we will explain the basic theory behind the WORKDAY function, how to use it in Excel, and how to create a scenario with real data to illustrate its application. We will also show you some other approaches to calculate workdays in Excel.
The WORKDAY function has the following syntax:
=WORKDAY(start_date, days, [holidays])
The function takes three arguments:
start_date: This is the date from which you want to start counting the workdays. It must be a valid Excel date, entered by using the DATE function or as a result of another formula or function.days: This is the number of workdays before or after the start date. It can be a positive or negative integer, depending on whether you want to calculate a future or past date.holidays: This is an optional argument that allows you to specify a list of dates that should be considered as non-working days, such as public holidays or floating holidays. It can be a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.
The WORKDAY function returns a serial number that represents a date that is the specified number of workdays before or after the start date, excluding weekends and holidays. By default, Excel considers Saturday and Sunday as weekend days, but you can use the WORKDAY.INTL function to customize the weekend days.
How to Use the WORKDAY Function in Excel
To use the WORKDAY function in Excel, follow these steps:
- Open a new or existing Excel workbook and select the cell where you want to display the result.
- Enter the formula
=WORKDAY(start_date, days, [holidays])in the selected cell, replacing the arguments with the appropriate values or cell references. For example,=WORKDAY(A2, B2, C2:C4). - Press Enter to calculate the result. Excel will display the date that is the specified number of workdays before or after the start date, excluding weekends and holidays. You can format the cell as a date to make it more readable.
- You can copy and paste the formula to other cells or use the fill handle to drag it down or across a range of cells.
Scenario: Calculate the Due Date of a Project
To illustrate how the WORKDAY function works in practice, let’s create a scenario with real data. Suppose you are a project manager and you need to calculate the due date of a project that has the following specifications:
- The project start date is January 1, 2024.
- The project duration is 120 workdays.
- The project has four holidays: January 17, February 21, May 29, and July 4.
To calculate the due date of the project, we can use the WORKDAY function as follows:
- In cell A1, enter the label “Project Start Date”.
- In cell B1, enter the date “1/1/2024” or use the formula
=DATE(2024,1,1). - In cell A2, enter the label “Project Duration (workdays)”.
- In cell B2, enter the number “120”.
- In cell A3, enter the label “Holidays”.
- In cells B3:B6, enter the dates of the holidays: “1/17/2024”, “2/21/2024”, “5/29/2024”, and “7/4/2024”. You can also use the DATE function to enter the dates, such as
=DATE(2024,1,17). - In cell A7, enter the label “Project Due Date”.
- In cell B7, enter the formula
=WORKDAY(B1, B2, B3:B6). - Press Enter to calculate the result. Excel will display the date “8/15/2024” as the project due date, excluding weekends and holidays. You can format the cell as a date to make it more readable.
The following table shows the data and the formula used in this scenario:
| A | B |
|---|---|
| Project Start Date | 1/1/2024 |
| Project Duration (workdays) | 120 |
| Holidays | 1/17/2024 |
| 2/21/2024 | |
| 5/29/2024 | |
| 7/4/2024 | |
| Project Due Date | =WORKDAY(B1, B2, B3:B6) |
Other Approaches to Calculate Workdays in Excel
The WORKDAY function is not the only way to calculate workdays in Excel. Here are some other approaches that you can use:
- The WORKDAY.INTL function: This function is similar to the WORKDAY function, but it allows you to specify which days of the week are considered as weekend days. You can use a number or a string to indicate the weekend days, such as 1 for Saturday and Sunday, 2 for Sunday and Monday, “0000011” for Saturday and Sunday, and so on. The syntax of the WORKDAY.INTL function is:
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
- The NETWORKDAYS function: This function returns the number of whole workdays between two dates, excluding weekends and holidays. You can use this function to calculate the project duration, given the start date and the end date. The syntax of the NETWORKDAYS function is:
=NETWORKDAYS(start_date, end_date, [holidays])
- The NETWORKDAYS.INTL function: This function is similar to the NETWORKDAYS function, but it allows you to specify which days of the week are considered as weekend days. The syntax of the NETWORKDAYS.INTL function is:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])