WORKDAY.INTLformula 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.
- How to use
- Examples of using
WORKDAY.INTLformula not working?
- Similar formulas to
WORKDAY.INTL formula with the syntax shown below, it has 2 required parameters and 2 optional parameters:
- start_date (required):
The starting date for the calculation.
- num_days (required):
The number of workdays to add or subtract from the starting date.
- 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.
- 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.
ExamplesHere are a few example use cases that explain how to use the
WORKDAY.INTLformula in Google Sheets.
Calculate due dates
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
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.
Calculate project deadlines
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.
WORKDAY.INTLnot working? Here are some common mistakes people make when using the
WORKDAY.INTLGoogle 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:
WORKDAYfunction 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.
NETWORKDAYSfunction 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.
DATEformula 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.
DATEDIFfunction 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.