Summary

The Excel WORKDAY function takes a date and returns the nearest working day n days in the future or past. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days.

Purpose 

Get a date n working days in the future or past

Return value 

A serial number representing a particular date in Excel.

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 

=WORKDAY(start_date, days, [holidays])

How to use 

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.

WORKDAY function examples

Excel formula: Get project midpoint

Get project midpoint

The WORKDAY function returns a date in the future or past, based on a start date, workdays, and optional holidays. WORKDAY automatically excludes weekends, and counts only Monday through Friday as workdays. In the example shown, WORKDAY is configured to get a project midpoint date by adding half of...
Excel formula: Get project end date

Get project end date

This formula uses the WORKDAY function to calculate an end date. WORKDAY can calculate dates in the future or past, and automatically excludes weekends and holidays (if provided). In the example shown, we have the project start date in column C, and days in column D. Days represents the duration of...
Excel formula: Next business day 6 months in future

Next business day 6 months in future

Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015. Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the range B9:B11...
Excel formula: Semimonthly pay schedule

Semimonthly pay schedule

In this example, the goal is to create a list of pay dates that follow a semimonthly schedule. A semimonthly pay schedule means employees are paid twice a month, usually on fixed dates such as the 1st and 15th or the 15th and the last day of the month. This results in 24 pay periods over the course...
Excel formula: Add business days to date

Add business days to date

By default, the WORKDAY function will exclude weekends (Saturday and Sunday). In this case, we have also supplied a list of 3 holidays that all fall in the date range being calculated, which means both the weekends and holidays will be excluded. The following table shows which dates are excluded...
Excel formula: Biweekly pay schedule

Biweekly pay schedule

In this example, the goal is to create a list of pay dates that follow a biweekly schedule. A biweekly pay schedule means employees are paid every two weeks on a given day of the week. Each pay period is 14 days, and there are usually 26 pay dates per year, though occasionally 27 depending on the...
Excel formula: Previous working day

Previous working day

In the worksheet shown, column B contains 12 dates. The goal is to calculate the previous working day before each date, taking into account weekends (Saturday and Sunday) and the holidays listed in column F. The formula should automatically skip weekends and any dates considered non-working days...
Excel formula: Date is workday

Date is workday

The WORKDAY function calculates dates in the future or past that are (by definition) "workdays". In other words, WORKDAY automatically excludes weekends and (optionally) holidays. WORKDAY accepts 3 arguments: start_date, days, and an (optionally) holidays. Since we want to check a single date and...
Excel formula: Due date by category

Due date by category

In this example, the goal is to create a due date based on category, where each category has a different number of days allocated to complete a given task, issue, project, etc. The amount of time available to resolve each category is shown in column H, and categories is the named range G5:H7. The...
Excel formula: Next working day

Next working day

In the worksheet shown, column B contains 12 dates. The goal is to calculate the next working day after each date, taking into account weekends (Saturday and Sunday) and the holidays listed in column F. In other words, the formula should automatically skip weekends and any dates defined as non-...
Excel formula: Get project start date

Get project start date

This formula is uses the WORKDAY function, which returns a date in the future or past, based on start date and required work days. WORKDAY automatically excludes weekends, and can also exclude holidays if provided as a range of dates. In the example shown, the project end date is in column C, and...
Excel formula: Random date between two dates

Random date between two dates

The RANDBETWEEN function takes two numbers, a bottom and top number, and generates a random integer in between. Dates in Excel are serial numbers, so you can use the DATE function to create t he lower number and the upper number. RANDBETWEEN then generates a number that falls between these two date...

WORKDAY function videos

How to calculate due dates with WORKDAY

In this video we'll look at how to calculate due dates with the WORKDAY and WORKDAY.INTL functions. The WORKDAY function returns a date in the future or past that takes into account weekends and, optionally, holidays. You can use the WORKDAY function to calculate things like ship dates, delivery...

How to generate random values

In this video we'll look at a few ways to generate random values with the RANDBETWEEN function. The RANDBETWEEN function is a simple function you can use to generate random numbers. For example, I can enter RANDBETWEEN with a bottom value of 1 and a top value of 100. When I press Enter, I get a...

Related functions

Excel WORKDAY.INTL function

WORKDAY.INTL Function

The Excel WORKDAY.INTL function takes a date and returns the "next" working day, based on a given offset value. Unlike the WORKDAY function, WORKDAY.INTL allows you to customize which days are considered weekends (non-working days). You can use...
Excel EDATE function

EDATE Function

The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the future, and a negative value for dates in the past.
Excel NETWORKDAYS function

NETWORKDAYS Function

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates. ...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Videos

Functions

Articles

Links