Part 1: Introduce
Definition
The WORKDAY function in Excel returns a date representing a specific number of working days before or after a given date (the starting date). Working days exclude weekends and any specified holidays.
Purpose
The WORKDAY function is commonly used to calculate invoice due dates, expected delivery times, or the number of days of work performed, excluding weekends or holidays.
Syntax & Arguments
The syntax for the WORKDAY function is as follows:
=WORKDAY(start_date, days, [holidays]) - start_date: Required. A date that represents the start date.
- Days: Required. The number of non-weekend and non-holiday days before or after the start date. A positive value yields a future date; a negative value yields a past date.
- Holidays: Optional. A list of one or more dates to exclude from the working calendar, such as state and federal holidays.
Explain the Arguments in the Function
- start_date: The initial date from which the calculation begins.
- Days: The number of working days to add or subtract from the start date.
- Holidays: A list of specific dates should be excluded from the calculation.
Return Value
The WORKDAY function returns a date that is the indicated number of working days before or after the start date.
Remarks
- Dates should be entered using the DATE function or as results of other formulas.
- If any argument is not a valid date, WORKDAY returns the #VALUE! Error.
- If the result yields an invalid date, WORKDAY returns the #NUM! Error.
- If days is not an integer, it is truncated.
Part 2: Examples
Example 1
Purpose of Example
Calculate the expected delivery date for an order, excluding weekends and holidays.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Holidays | Formula | Result |
| 2 | 1 | 2023-01-01 | 10 | 2023-01-06 | =WORKDAY(B2, C2, D2) | 2023-01-15 |
Explanation
This example calculates the expected delivery date as 10 working days from January 1, 2023, excluding the holiday on January 6, 2023. The result is January 15, 2023.
Example 2
Purpose of Example
Calculate the due date for an invoice, excluding weekends.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Formula | Result |
| 2 | 1 | 2023-01-01 | 30 | =WORKDAY(B2, C2) | 2023-02-11 |
Explanation
This example calculates the due date for an invoice 30 working days from January 1, 2023. The result is February 11, 2023.
Example 3
Purpose of Example
Calculate the project completion date, excluding weekends and multiple holidays.
Data Tables and Formulas
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Holiday 1 | Holiday 2 | Formula | Result |
| 2 | 1 | 2023-01-01 | 15 | 2023-01-06 | 2023-01-20 | =WORKDAY(B2, C2, D2:E2) | 2023-01-22 |
Explanation
This example calculates the project completion date as 15 working days from January 1, 2023, excluding the holidays on January 6 and 20, 2023. The result is January 22, 2023.
Example 4
Purpose of Example
Calculate the start date of a new project, considering a delay of 5 working days, excluding weekends.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Formula | Result |
| 2 | 1 | 2023-01-01 | -5 | =WORKDAY(B2, C2) | 2022-12-23 |
Explanation
This example calculates the start date of a new project, considering a delay of 5 working days from January 1, 2023. The result is December 23, 2022.
Example 5
Purpose of Example
Calculate the expected shipment date for multiple orders, excluding weekends and a typical holiday.
Data Tables and Formulas
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Holiday | Formula | Result | |
| 2 | 1 | 2023-01-01 | 7 | 2023-01-06 | =WORKDAY(B2, C2, D2) | 2023-01-10 | |
| 3 | 2 | 2023-01-10 | 10 | 2023-01-06 | =WORKDAY(B3, C3, D3) | 2023-01-24 |
Explanation
This example calculates the expected shipment dates for two orders, excluding weekends and a typical January 6, 2023, holiday. The results are January 10, 2023, and January 24, 2023, respectively.
Example 6: Using IF with WORKDAY
Purpose of Example
Determine if a project’s end date falls within the expected timeframe.
Data Tables and Formulas
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Target Date | Formula | Result | Status |
| 2 | 1 | 2023-01-01 | 10 | 2023-01-15 | =WORKDAY(B2, C2) | 2023-01-15 | =IF(E2<=D2, "On Time", "Delayed") |
Explanation
This example calculates the end date of 10 working days from January 1, 2023, and compares it to a target date. The IF function determines if the project is on time or delayed. Since the result matches the target date, the status is “On Time.”
Example 7: Using SUM with WORKDAY
Purpose of Example
Calculate the total working days for multiple projects.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | Total |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =WORKDAY(B2, 30) | 2023-02-11 | |
| 3 | 2 | 2023-03-01 | 2023-04-30 | =WORKDAY(B3, 40) | 2023-04-20 | =SUM(C2:C3) |
Explanation
This example calculates the end dates for two different project durations and then uses the SUM function to calculate the total working days for both projects. The total working days for both projects are 70.
Example 8: Using VLOOKUP with WORKDAY
Purpose of Example
Calculate the project end date and look up the corresponding project status from a reference table.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Formula | Result | Status |
| 2 | 1 | 2023-01-01 | 30 | =WORKDAY(B2, C2) | 2023-02-11 | =VLOOKUP(E2, H2:I4, 2, FALSE) |
| H | I | |
|---|---|---|
| 1 | Date | Status |
| 2 | 2023-02-11 | On Schedule |
| 3 | 2023-02-15 | Ahead |
Explanation
This example calculates the project end date 30 working days from January 1, 2023, and then uses the VLOOKUP function to look up the corresponding status from a reference table. Since the result is February 11, 2023, the status is “On Schedule.”
Example 9: Using AVERAGE with WORKDAY
Purpose of Example
Calculate the average end date for multiple projects.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Formula | Result | Average |
| 2 | 1 | 2023-01-01 | 30 | =WORKDAY(B2, C2) | 2023-02-11 | |
| 3 | 2 | 2023-03-01 | 40 | =WORKDAY(B3, C3) | 2023-04-20 | =AVERAGE(C2:C3) |
Explanation
This example calculates the end dates for two different project durations and then uses the AVERAGE function to calculate the average working days for both projects. The average working days for both projects are 35.
Example 10: Using CONCATENATE with WORKDAY
Purpose of Example
Calculate the project end date and create a summary statement.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Formula | Result | Summary |
| 2 | 1 | 2023-01-01 | 30 | =WORKDAY(B2, C2) | 2023-02-11 | =CONCATENATE("The project ends on ", TEXT(E2, "mm/dd/yyyy"), ".") |
Explanation
This example calculates the project end date 30 working days from January 1, 2023, and then uses the CONCATENATE function to create a summary statement. The summary is “The project ends on 02/11/2023.”
Example 11: Using MAX with WORKDAY
Purpose of Example
Calculate the maximum end date among multiple projects.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Formula | Result | Max Date |
| 2 | 1 | 2023-01-01 | 30 | =WORKDAY(B2, C2) | 2023-02-11 | |
| 3 | 2 | 2023-03-01 | 40 | =WORKDAY(B3, C3) | 2023-04-20 | =MAX(E2:E3) |
Explanation
This example calculates the end dates for two different project durations and then uses the MAX function to find the maximum end date among the projects. The maximum end date is April 20, 2023.
Example 12: Using MIN with WORKDAY
Purpose of Example
Calculate the minimum end date among multiple projects.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Formula | Result | Min Date |
| 2 | 1 | 2023-01-01 | 30 | =WORKDAY(B2, C2) | 2023-02-11 | |
| 3 | 2 | 2023-03-01 | 40 | =WORKDAY(B3, C3) | 2023-04-20 | =MIN(E2:E3) |
Explanation
This example calculates the end dates for two different project durations and then uses the MIN function to find the minimum end date among the projects. The minimum end date is February 11, 2023.
Example 13: Using COUNTIF with WORKDAY
Purpose of Example
Calculate the number of projects that meet a specific end date target.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | Days | Formula | Result | Target Met |
| 2 | 1 | 2023-01-01 | 30 | =WORKDAY(B2, C2) | 2023-02-11 | |
| 3 | 2 | 2023-03-01 | 40 | =WORKDAY(B3, C3) | 2023-04-20 | =COUNTIF(E2:E3, "<="&DATE(2023,3,1)) |
Explanation
This example calculates the end dates for two different project durations and then uses the COUNTIF function to count the number of projects that end on or before March 1, 2023. The number of projects that meet the target is 1.
Part 3: Tips and Tricks
- Use the DATE Function: Always use the DATE function or other formulas to enter dates to avoid errors.
- Consider the WORKDAY.INTL Function: If you need to specify custom weekends, consider using WORKDAY.INTL function.
- Utilize Named Ranges: If you have a standard list of holidays, consider using a named range to make your formulas cleaner and more manageable.
- Check for Errors: Be mindful of potential errors like #VALUE! and #NUM! and understand what they mean to troubleshoot effectively.
- Combine with Other Functions: The WORKDAY function can be combined with other Excel functions to create more complex calculations.