Excel WORKDAY.INTL function

If you want to follow along with this tutorial, please download the example spreadsheet.

Description

The WORKDAY.INTL function adds a specified number of workdays to a start date and returns a workday in the future or past. Also, it can exclude custom holidays when calculating as you need.

Syntax and arguments

Formula syntax

Arguments

Weekend Codes

Weekend String

A series of seven zeros and ones which are used to indicates weekend days. Each character indicates one weekday, the first one is Monday, the second one is Tuesday, the third one is Wednesday, the last one is Sunday. For example:

Return Value

The WORKDAY.INTL function returns a date in serial number format.

Remarks

1.If the argument days is a decimal value, the WORKDAY function only adds the integer number to the start_date.

2. While referring a range of cells as argument holidays, you’d better use the absolute reference or a range name, otherwise, when you drag fill handle over cells to use a formula, the reference will be changed.

Errors

#NUM! error occurs when:

• Invalid argument start_date;
• Invalid argument days;
• Invalid argument weekends.

#VALUE! error occurs when:

• Argument start_date, holidays are invalid dates;
• Argument days is non-numeric;
• Argument weekend is invalid text string.

Usage and Examples

Example 1 Calculate the future or past workday with default weekends and without holidays

To get the working day based on the given start date in column B and the days in column C, please use below formula:

=WORKDAY.INTL(B3,C3)

Press Enter key and drag the autofill handle over the cells you need to apply this formula, and get serial numbers.

Then format the serial numbers as date format as you need. Keep the serial numbers selected, click Home > Number Format to select Short Date or Long Date from the drop-down list.

Example 2 Calculate the workday with custom weekends and excludes holidays

For example, start dates list in the column B, the days list in the column C, holidays are in the column F, and weekend days are Monday and Tuesday, please use below formula:

=WORKDAY.INTL(B3,C3,3,$F$3:$F$5)

Press Enter key and drag the autofill handle over the cells you need to apply this formula, and get serial numbers.

Then format the serial numbers as date format as you need. Keep the serial numbers selected, click Home > Number Format to select Short Date or Long Date from the drop-down list.

Relative Functions:

• Excel Days Function
  To calculate the number of days between two given dates, the DAYS function in Excel can help you.

• Excel DAYS360 Function
  DAYS360 function can help you to return the number of days between two dates based on a 360-day year, which is considered to have 30 days in each month.

• Excel WEEKNUM Function
  The WEEKNUM in Excel returns the week number of the given date in a year, which starts counting weeks from January 1.

• Excel WORKDAY;Function
  The WORKDAY is used to add a specified number of workdays to a started date and returns the future or past date in a serial number format.