Purpose
Return value
Arguments
- start_date - The date from which to start.
- days - The working days before or after start_date.
- holidays - [optional] A list dates that should be considered non-work days.
Syntax
Usage notes
The WORKDAY function returns the nearest working day n days in the future or past. WORKDAY can be used to calculate due dates, delivery dates, and other dates that should exclude non-working days.
The WORKDAY function takes three arguments: start_date, days, and holidays. Start_date must be a valid Excel date. The days argument is the number of days in the future or past to calculate a workday. Use a positive number for days to calculate future dates, and a negative number for past dates. Holidays is an optional argument to specify non-working days. Holidays should be provided as a range that contains valid Excel dates. If holidays are not provided, WORKDAY will treat only Saturdays and Sundays as non-working days.
When calculating a result, WORKDAY does not include the start date as a work day.
Example #1 - Basic usage
In the formula below, WORKDAY is given Friday, January 1, 2021 for start_date, 1 for days, and F5:F13 for holidays. The result is Monday, January 4, 2021 since Saturday and Sunday are excluded:
=WORKDAY("1-Jan-2021",1,F5:F13)// returns 4-Jan-2021
If the start date is moved back one day to Thursday, December 31, 2021, the result is the same, since January 1 is a holiday, and Saturday and Sunday are also excluded:
=WORKDAY("31-Dec-2020",1,F5:F13) // returns 4-Jan-2021
Example #2 - worksheet as shown
In the worksheet shown above, Column B contains a variety of different start dates, column C contains the number of days to move, and "holidays" are the named range F5:F13. The formula in column D (copied down) is:
=WORKDAY(B5,C5,holidays)
At each row, WORKDAY returns the nearest workday in column D, based on the given start date and days to offset.
Weekends
By default, WORKDAY will exclude weekends (Saturday and Sunday) . If you need to customize which days of the week are considered weekend days, use the more robust WORKDAY.INTL function.