Excel WORKDAY Function Examples

How to calculate schedules in Excel with the WORKDAY function and WORKDAY.INTL function. See how the functions work, get Excel workbook with examples

Author: Debra Dalgleish

Introduction

If you’re trying to figure out when a business project will be completed, you can use the WORKDAY function, in any version of Excel.

Or, in Excel 2010 or later, use the WORKDAY.INTL function

• With WORKDAY, weekend (non-working) days are always Saturday and Sunday
• With WORKDAY.INTL, you can customize the non-working days

In the sections below, there are details and examples for both WORKDAY functions.

Excel WORKDAY Function

For working day calculations where the weekends are always Saturday and Sunday, you can use the Excel WORKDAY function. This function can be used in any version of Excel.

WORKDAY Arguments

In the WORKDAY syntax, there are 2 required arguments, and 1 optional argument:

• start_date: starting date for the calculation
• days: number of whole days before or after the start date
• holidays: (optional) non-working calendar dates

Notes About WORKDAY

Here are a few notes about working with the WORKDAY function:

Start Date

• Start date will be truncated to an integer
  • For example, if you enter a date and time, the time will be ignored
• Make sure the start date is a working day, or results might be different from what you expect

Days

• Number of days will be truncated to an integer
• Number can be positive, negative or zero
  • positive number to move forward
  • negative number to move backward
  • zero to return the start date

Holidays

• This argument is optional. If omitted, no calendar dates are treated as special non-working days.

List Upcoming Workdays

In this example, people need to pick a delivery date that falls on one of the next 3 workdays.

• First, use the WORKDAY function, to create a short list with the dates of upcoming workday.
  • That lets people quickly see their options - nobody has to check a calendar
• Next, to make data entry even easier, make a drop down list to choose one of those valid dates

The step-by-step instructions and screen shots are below.

Calculate Dates with WORKDAY

For this WORKDAY function example, there are 2 different formulas used to create the list of upcoming working days

1. Current Date
2. Upcoming Workdays

Find the Current Date

First, in cell C2, a formula uses the TODAY function to calculate the current date:

• =TODAY()

That date will automatically update every day, when you open the workbook.

Calculate Upcoming Workdays

Next, the WORKDAY function calculates the first 3 upcoming workdays.

• In cells B5, B6 and B7, type the numbers 1, 2, and 3
• In cell C5, enter the following formula:
  • =WORKDAY($C$2, B5)
• Then, copy the formula down to cells C6 and C7

In each cell, the WORKDAY function:

• starts with the current date, in cell C2
• adds the number of days, from column B, in its row
• returns the date of that working day

NOTE: Because cell C2 automatically updates every day, the list will always show the correct list of upcoming workdays.

Show the Weekday Name

After the formulas are in place, format the dates, so they show the weekday name, as well as the month, day and year.

This step is optional, but can be helpful to people working with the list.

• Select all 4 formula cells in column B, where the dates are calculated
• Press Ctrl+1 to open the Format Cells dialog box
• In the Category list, click on Custom
• In the Type box, enter this custom number format, or something similar, to include the weekday name:
  • ddd mm/dd/yyyy
• Click OK, to apply the custom number format

Drop Down List of Dates

Next, if you want to create a drop down list of the upcoming dates, the following sections show how to:

1. Create a name for the list of dates, so it's easy to refer to those cells
2. Use data validation to make a drop down list that shows the dates

Name the List of Dates

The dates that are calculated by the formulas in cells B5:b7 will be used in a drop down list.

To make it easy to refer to the list of dates, follow these steps to name that range of cells:

• Select cells B5:B7
• Click in the Name Box, at the left of the Formula bar
• Type a one-word name for the list: DateListWork
• Press the Enter key, to complete the name
  • Tip: If you forget to press Enter, the name will NOT be created

Create the Drop Down List

The final step is to create a drop-down list of of those upcoming dates, so people can select one easily.

Follow these steps, to create a data validation drop down list:

• Select the cell where you want the drop down list - cell B3, in this example
• On the Ribbon at the top of Excel, click the Data tab
• In the Data Tools group, click the Data Validation command
• In the Data Validation dialog box, in the Allow drop down, select List
• In the Source box, type an equal sign, then the name of the date list:
  • =DateListWork
• Click OK, to create the drop down list

Format the Drop Down List Cell

While you still have the drop down list cell selected, make these formatting changes:

• Apply the same custom number format that you used on the date formula cells, to show the date and weekday name
  • Tip: Use the Format Painter to copy the formatting from the Today formula cell
• Add a border and fill colour to the cell, so it stands out on the worksheet

Use the Drop Down List

Now, when you click on the where you created the drop down list, an arrow appears at the right side of the cell.

Click that arrow, and select one of the dates.

More Data Validation Examples

See more examples of data validation for dates on my Contextures site.

There's a video on that page too, and a sample file to download.

Project Task Dates with WORKDAY

In the video below, the WORKDAY function is used in column C, to calculate start and end dates for project tasks.

Find Task Start Dates

This is the formula in cell C6, to calculate the start date for the first task:

• =WORKDAY(MAX(C5,$B$2), SUM(D5), HolidayList)

Make a Gantt Chart

Next, a simple Gantt chart is created from those task start dates, that the WORKDAY function calculated.

The written steps, and the sample file for this video, are on the Excel Gantt Chart Project Plan page.

WORKDAY.INTL Function

If you’re trying to figure out when a project will be completed, you can use the WORKDAY.INTL function (Excel 2010 or later).

It’s more flexible than the older WORKDAY function – it doesn’t assume weekends are always Saturday and Sunday. You can choose other weekend days, or create a custom list of non-working days.

WORKDAY.INTL Arguments

In the WORKDAY.INTL syntax, there are 2 required arguments, and 2 optional arguments:

• start_date: starting date for the calculation
• days: number of whole days before or after the start date
• weekend: (optional) code to indicate non-working days
• holidays: (optional) non-working calendar dates

Notes About WORKDAY.INTL

Here are a few notes about working with the WORKDAY.INTL function:

Start Date

• Start date will be truncated to an integer
• For example, if you enter a date and time, the time will be ignored
• Make sure the start date is a working day, or results might be different from what you expect

Days

• Number of days will be truncated to an integer
• Number can be positive, negative or zero
  • positive number to move forward
  • negative number to move backward
  • zero to return the start date

Weekend

• This argument is optional. If omitted, non-working days are Saturday and Sunday
• To enter this argument:
  • select from the drop down list of options
  • OR, create a 7-digit string with 1 (non-working) and 0 (working) characters

Holidays

• This argument is optional. If omitted, no calendar dates are treated as special non-working days.

Project End Date

In this example, see how to use the WORKDAY.INTL function, so you can calculate a project end date. This function is similar to WORKDAY, but you can customize the non-working days.

This example will:

1. Start with the required arguments for WORKDAY.INTL - start date and days.
2. After that, a holiday list will be added.
3. As the final step, non-working days will be specified

Start with Basics

To use the basic features of the WORKDAY.INTL function, you can enter 2 pieces of information:

• project start date
• number of days required to complete the project

Then, WORKDAY.INTL will calculate the working date that is the specified number of after the start date.

In the screen shot below, we start on Thursday, December 10th (cell C8), and the project will take 2 days (cell C10).

The WORKDAY.INTL formula in cell C12 is:

• =WORKDAY.INTL(C8,C10)

Check the Calculation

The table below shows why the result is December 14th.

• We work on the project on Thursday and Friday (2 days).
• Since we didn’t specify weekend days, it assumes that we don’t work on Saturday or Sunday.
• The next working day is Monday, December 14th, so that is the formula result.

Adjust the End Date

I’d rather see the date that the project will be completed, instead of the next working day. So, to adjust the result, I subtract 1 from the number of working days in the formula.

=WORKDAY.INTL(C8,C10-1)

That changes the formula result to Friday, December 11th – the day we stop working on the project.

Exclude Holidays

Just like the older WORKDAY function, you can create a list of holidays, and tell the WORKDAY.INTL function to exclude those dates.

If you use a named Excel table, it’s easy to add or remove dates, when necessary. In the screen shot below, the table is named tblHol, and the date column is used as the Holiday List.

To exclude those holidays from the date calculations, I’ll add that reference in the fourth argument of the function.

=WORKDAY.INTL(C8,C10-1,,tblHol[Date])

With that adjustment, if we start a project on Thursday, December 24th, the end date will be Monday, December 28th.

Here is the table that shows the working days, with the holidays highlighted in orange.

Specify Non-Working Days

If you don’t specify which days are the weekend, the WORKDAY.INTL function automatically excludes Saturday and Sunday as weekend (non-working) days. However, you can select different days as the weekend, with either of the following methods:

• select from the drop down list of options
• create a string with 1 and 0 characters

Select from Drop Down List

The easier way to specify weekend days is to select from the drop down list of options. The list will appear automatically when you start the 3rd argument in the function.

NOTE: If the list doesn’t appear, press Alt + Down Arrow

If I choose option 2 – Sunday, Monday – the project end date changes to Tuesday, December 29th.

=WORKDAY.INTL(C8,C10-1,2,tblHol[Date])

Create Non-Working Day String

If none of the options in the drop down list are what you need, you can create your own string.

• The string must be 7 digits, representing the weekdays, from Monday to Sunday
• Use a 0 for working days
• Use a 1 for non-working days

For example, if you only work Monday, Wednesday and Friday, use the string 0101011

I’ll change the start date to a working date in our new schedule – Wednesday, December 23rd. Here is the revised formula, with the 7-digit string in the 3rd argument, inside double quote marks:

=WORKDAY.INTL(C8,C10-1,”0101011″,tblHol[Date])

With those changes, the end date is Monday, December 28th.

Calculate the Non-Working String

If you want to make it easier to select the non-working days, you can use a table, like the one below. Mark the non-working days with an X, and use an IF formula to show 1 or 0 in each row.

=IF(K8=”x”,1,0)

Then, use a CONCATENATE formula to compile the string:

=CONCATENATE(M8,M9,M10,M11,M12,M13,M14)

In the WORKDAY.INTL formula, refer to that cell in the 3rd argument.

=WORKDAY.INTL(C8,C10-1,M15,tblHol[Date])

The calculation columns could be hidden, to prevent people from messing up the formulas.

Nth Weekday of Month - WORKDAY.INTL

The WORKDAY.INTL function, with a custom string of non-working days, can be used to calculate the Nth weekday in a month.

This video shows how to find the Nth weekday in a specific month and year, by using the WORKDAY.INTL function in an Excel formula. For example, Canadian Thanksgiving is the 2nd Monday in October, so what is the date for the current year.

There is an example, below the video, and detailed steps on on the Nth Weekday of Month page..

Nth Weekday of Month Example - Thanksgiving USA

For example, if USA Thanksgiving is the 4th Thursday in November, what date will that be this year?

In the screen shot below, this formula is in cell C10, to calculate the Thanksgiving date:

• =WORKDAY.INTL(DATE(C4,D5,0),C7,"1110111")

With the 7-digit string of non-working days, only the 4th digit (Thursday) is a working day each week.

So, the formula returns the date of the 4th Thursday in November, as the correct Thanksgiving date

Get more details on how this formula works, and the sample workbook, on the Nth Weekday of Month page.

Download the Workbook

WORKDAY Function Examples: Download the sample file with WORKDAY and WORKDAY.INTL examples from this tutorial. Note - WORKDAY.INTL function available in Excel 2010 or later. Zipped Excel file is in xlsx format, and does not contain macros.

Related Tutorials

Date Functions

Find Nth Weekday in Month

Excel Gantt Chart

Data Validation - Dates