|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

WORKDAY

WORKDAY

  • Last updated on

Description 

Use this function to determine a date a given number of working days in the future or past from a specified date.

You can also specify both which days are to be considered weekend (non-working) days and the holidays to be excluded. This function us useful for calculating start dates, delivery dates, and completion dates that need to factor in both working and non-working days.

 

Syntax

WORKDAY(start_date,days,[holidays])

 

Inputs

This function has the following arguments:

Name Required Description Valid input
start_date Yes Date to be used as the base for calculation.  This can be a cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD.
A formula which results in one of these is also acceptable. Quotation marks are required.
days Yes Working days before or after start_date.  This can be a cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD.
A formula which results in one of these is also acceptable. Quotation marks are required.
holidays No A list of non-work days as supplied as dates. 


This can be a cell reference, a cell range (D2:D13), an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. 
A formula which results in one of these is also acceptable.

If holidays are not provided, WORKDAY will treat only Saturdays and Sundays as non-working days.

 

Example

Sample data

  A B C D E
1 Start Date Offset Days Holidays WORKDAY result Expedited shipping
2 1/1/2024 14   1/19/2024 Yes
3 1/1/2024 14 1/11/2024 1/22/2024 No
4 1/1/2024 -14   12/12/2023 No
5 7/19/2023 9 12/31/2023 8/01/2023 Yes
6 45283 12 1/8/2024 01/08/2014 Yes

Sample formulas

Use case Formula Explanation and Result
Determine the date that is the specified number of workdays (Mon-Fri) after the specified date, with no holidays specified. =WORKDAY("1-Jul-2024",5)

This formula works in the following manner:

  • "1-Jul-2024" is the specified date.
  • 5 is the number of workdays to be added to the specified date.

Result: 45481 (8 July 2024).
Determine the date that is the specified number of workdays (Mon-Fri) after the date specified in a cell, with no holidays specified. =WORKDAY(A2,B2)

This formula works in the following manner:

  • Cell A2 is the specified date. (1/1/2024)
  • B2 is the number of workdays to be added to the specified date. (14)

Result: 45300 (1/9/2024).
Determine the date that is the specified number of workdays (Mon-Fri) between the two dates specified in two cells, with no holidays specified. =WORKDAY(A2,-14)

This formula works in the following manner:

  • Cell A2 is the specified date. (1/1/2024)
  • -14 is the number of workdays to be added to the specified date.

Result: 45272 (12/12/2023).
Determine the date that is the specified number of workdays (Mon-Fri) between the two dates specified in two cells, using the holidays specified in a third cell. =WORKDAY(A3,B3,C3)

This formula works in the following manner:

  • Cell A3 is the specified date. (1/1/2024)
  • Cell B3 is the number of workdays to be added to the specified date. (14)
  • Cell C3 is the holiday date to be subtracted. (1/11/2024)

Result: 45308 (1/22/2024).
Determine the date that is the specified number of workdays (Mon-Fri) between the two dates specified in two cells, using the holidays specified in an additional cell range. =WORKDAY(A2,B2,C2:C6)

This formula works in the following manner:

  • Cell A3 is the specified date. (1/1/2024)
  • Cell B3 is the number of workdays to be added to the specified date. (14)
  • Cell C3 is the holiday date to be subtracted. (1/11/2024)

Result: 45324 (2/2/2024).
Calculate a project's start date by subtracting a specific number of workdays from a deadline. =WORKDAY(DATE(2024,3,15),-10)

This formula works in the following manner:

  • DATE(2024, 3, 15) is the deadline date. (March 15, 2024)
  • WORKDAY(DATE(2024, 3, 15),-10) calculates the start date, 10 workdays before the deadline.

Result: 45352 (3/1/2024).
Determine a revised project deadline by adding workdays to the original start date, considering the actual work duration (in network days). =WORKDAY(DATE(2024,3,1),NETWORKDAYS(DATE(2024,3,1), DATE(2024,3,15)))

This formula works in the following manner:

  • DATE(2024,3,1) is the original start date.
  • NETWORKDAYS(DATE(2024,3,1),DATE(2024,3,15)) calculates the number of workdays between the two supplied dates.
  • WORKDAY(…) calculates the revised deadline by adding the network days to the start date.

Result: 45369 (3/18/2024).
Determine a delivery date adjustment based on whether a condition is met (e.g., expedited shipping being selected). =IF(A2="Yes", WORKDAY(DATE(2024,3,8),2), WORKDAY(DATE(2024,3,8),5))

This formula works in the following manner:

  • Cell A2 contains "Yes" value (indicating expedited shipping).
  • IF(A2="Yes",…) calculates the delivery date. Because the value in A2 is "Yes," it adds 2 workdays to (DATE(2024,3,8),2).

Result: 45363 (3/12/2024).

 

Notes

  • WORKDAY
    • doesn't implicitly know which days are holidays. These have to be specified by the user.
    • includes the start date when calculating workdays.
    • automatically excludes all Saturdays and Sundays.
    • ignores any time values.
  • If the holiday date(s) are not between the specified start and end dates, they're ignored.
  • If any of the provided date(s) are not valid, WORKDAY returns the #VALUE! error.
  • Wildcards don't work with this function. 

 

Tips

  • It's generally easier to put holiday dates in a cell range and reference that range.

 

Related functions

Description Use this function to determine a date a given number of working days in the future or past from a specifi...