WORKDAY.INTL

The `WORKDAY.INTL` formula calculates the date that is a specified number of workdays away from a given starting date, using custom weekend parameters and an optional list of holiday dates. This formula is commonly used to calculate due dates or project deadlines, taking into account non-working days such as weekends and holidays.

Usage

Use the `WORKDAY.INTL` formula with the syntax shown below, it has 2 required parameters and 2 optional parameters:

Parameters:
1. start_date (required):
The starting date for the calculation.
2. num_days (required):
The number of workdays to add or subtract from the starting date.
3. weekend (optional):
An optional number or string representing the days of the week that are considered weekend days. By default, Saturday and Sunday are considered weekend days. The value should be an integer where 1 = Sunday, 2 = Monday, ..., 7 = Saturday. Alternatively, you can provide a string of 7 characters, each representing a day of the week with 1 indicating a weekend day and 0 indicating a workday.
4. holidays (optional):
An optional range or array of dates representing the holiday dates that should be excluded from the calculation. These dates should be valid dates in the same format as the start_date.

Examples

Here are a few example use cases that explain how to use the `WORKDAY.INTL` formula in Google Sheets.

Calculate due dates

Use `WORKDAY.INTL` to calculate due dates for tasks or projects, taking into account weekends and holidays. For example, if a task takes 5 workdays to complete and you want to know when it will be done, you can use `WORKDAY.INTL` to add 5 workdays to the start date.

Calculate end of financial year

Use `WORKDAY.INTL` to calculate the end of the financial year, taking into account weekends and public holidays. For example, if the financial year ends on June 30 and you want to know the date that is 10 workdays before the end of the financial year, you can use `WORKDAY.INTL` with the appropriate weekend and holiday parameters.

Use `WORKDAY.INTL` to calculate project deadlines, taking into account weekends and holidays. For example, if a project has a 30-day deadline and there are 3 public holidays during that period, you can use `WORKDAY.INTL` to determine the date by which the project must be completed.

Common Mistakes

`WORKDAY.INTL` not working? Here are some common mistakes people make when using the `WORKDAY.INTL` Google Sheets Formula:

Incorrect Date Format

The start_date parameter must be entered in a valid date format, such as '12/31/2021'. If the date is not in this format, the formula will return an error. To correct this, enter the date in a valid format.

Invalid Input for Weekend Parameter

The weekend parameter is optional, but if used, it must be a number between 1 and 7 representing the days of the week (1 = Sunday, 2 = Monday, etc.). If an invalid number is used, the formula will return an error. To correct this, enter a valid number between 1 and 7.

Num_days Parameter is Negative

The num_days parameter must be a positive number. If a negative number is used, the formula will return an error. To correct this, enter a positive number.

Incorrect Input for Holidays Parameter

The holidays parameter is optional, but if used, it must be a range or array of dates. If an invalid input is used, such as a single date or text, the formula will return an error. To correct this, enter a valid range or array of dates.

Incorrect Order of Parameters

The parameters must be entered in the correct order: start_date, num_days, [weekend], [holidays]. If the order is incorrect, the formula will return an error. To correct this, enter the parameters in the correct order.

The following functions are similar to `WORKDAY.INTL` or are often used with it in a formula:

• `WORKDAY`

The `WORKDAY` function returns a date that is a specified number of workdays (excluding weekends and holidays) away from a given start date. This function is useful for calculating a project deadline or delivery date. The function takes the start date and the number of workdays to add to the start date. Additionally, you can specify a range of holiday dates to exclude from the calculation.

• `NETWORKDAYS`

The `NETWORKDAYS` function returns the number of working days between two given dates. This function excludes weekends (Saturday and Sunday) and optionally, any specified holidays. This function is commonly used in financial and business applications to calculate the number of workdays between two dates.

• `DATE`

The `DATE` formula is used to create a date object in Google Sheets. It takes three arguments: year, month, and day and returns a date. This formula is useful when you need to perform date calculations or create date-based charts and graphs.

• `DATEDIF`

The `DATEDIF` function in Google Sheets calculates the difference between two dates using a specified unit of measurement. This function is commonly used to calculate the length of time between two dates, such as the number of days, months, or years. The function is particularly useful when working with financial or project planning data.

You can learn more about the `WORKDAY.INTL` Google Sheets function on Google Support.