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
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
In the sections below, there are details and examples for both WORKDAY functions.
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.
In the WORKDAY syntax, there are 2 required arguments, and 1 optional argument:
Here are a few notes about working with the WORKDAY function:
In this example, people need to pick a delivery date that falls on one of the next 3 workdays.
The step-by-step instructions and screen shots are below.
For this WORKDAY function example, there are 2 different formulas used to create the list of upcoming working days
First, in cell C2, a formula uses the TODAY function to calculate the current date:
That date will automatically update every day, when you open the workbook.
Next, the WORKDAY function calculates the first 3 upcoming workdays.
In each cell, the WORKDAY function:
NOTE: Because cell C2 automatically updates every day, the list will always show the correct list of upcoming workdays.
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.
Next, if you want to create a drop down list of the upcoming dates, the following sections show how to:
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:
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:
While you still have the drop down list cell selected, make these formatting changes:
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.
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.
In the video below, the WORKDAY function is used in column C, to calculate start and end dates for project tasks.
This is the formula in cell C6, to calculate the start date for the first task:
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.
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.
In the WORKDAY.INTL syntax, there are 2 required arguments, and 2 optional arguments:
Here are a few notes about working with the WORKDAY.INTL function:
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:
To use the basic features of the WORKDAY.INTL function, you can enter 2 pieces of information:
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:
The table below shows why the result is December 14th.
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.
That changes the formula result to Friday, December 11th – the day we stop working on the project.
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.
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.
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:
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.
If none of the options in the drop down list are what you need, you can create your own string.
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:
With those changes, the end date is Monday, December 28th.
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.
Then, use a CONCATENATE formula to compile the string:
In the WORKDAY.INTL formula, refer to that cell in the 3rd argument.
The calculation columns could be hidden, to prevent people from messing up the formulas.
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:
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.
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.
Last updated: April 1, 2022 10:37 AM