Definition
The WORKDAY.INTL is a powerful Excel formula primarily utilized to determine a date after a given number of workdays, excluding weekends and optionally holidays. It’s an improvement over the standard WORKDAY formula, as it provides flexibility in defining which days of the week are considered weekends.
Purpose
The primary purpose of the WORKDAY.INTL formula is to simplify project and task scheduling by allowing you to consider different workweek configurations. Whether your workweek is Sunday through Thursday or Tuesday through Saturday, this formula has you covered.
Syntax
The syntax of the WORKDAY.INTL formula is as follows:
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
Parameters
The WORKDAY.INTL formula consists of four parameters:
start_date: This is the starting date of the project. It is a mandatory parameter.days: This is the number of workdays for the task. It is also a required parameter.weekend: This optional parameter is used to specify the days of the week that are considered weekends.holidays: An optional list of any dates to exclude, such as public holidays.
Returns
The WORKDAY.INTL formula returns a date value. This date value is the end date of a task or project considering the specified number of workdays, weekends, and optional holidays.
Usage Notes
While using the WORKDAY.INTL formula, remember that it only considers the date portion of the datetime value. Also, if no weekend parameter is specified, the formula will consider Saturday and Sunday as the weekend by default.
Availability
The WORKDAY.INTL formula is available in Excel 2010 and later versions. It’s a great tool to enhance your spreadsheet calculator and advanced Excel functions for data analysis capabilities.
Example #1
If your project begins on 2023-06-01 (cell A1) and lasts for 10 workdays, and you consider Friday and Saturday as the weekend, use:
=WORKDAY.INTL(A1, 10, 7, )
This formula will return 2023-06-15, ten workdays after the start date.
Example #2
If you need to account for holidays, input them in the ‘holidays’ parameter. If you have a holiday on 2023-06-08 in cell B1:
=WORKDAY.INTL(A1, 10, 7, B1)
The formula will return 2023-06-18, accounting for the holiday.
Example #3
If the start date contains time information, such as 2023-06-01 08:00:00 in cell A1:
=WORKDAY.INTL(A1, 10, 7)
Excel only considers the date and will return 2023-06-15.
Example #4
For invalid dates, like “Hello” in cell A1:
=WORKDAY.INTL(A1, 10, 7)
Excel will return a #VALUE! error, signaling the input is not a valid date.
Example #5
If you want to calculate the start date given the end date and the duration, use WORKDAY.INTL with a negative ‘days’ value. If the end date is 2023-06-15 in cell A1:
=WORKDAY.INTL(A1, -10, 7)
This will return 2023-06-01, ten workdays before the end date, considering Friday and Saturday as weekends.
Tips and Tricks
Always remember to input the weekend string correctly. Each digit in the string represents a day of the week starting from Monday. For example, “1111110” represents Monday to Saturday as weekend days, and Sunday as a workday.
Limitations
The WORKDAY.INTL formula doesn’t include the start date in the calculation. If you want to include it, subtract one from the ‘days’ parameter.
Common Errors and Solutions
You might encounter the #VALUE! error if your ‘start_date’ is not a valid date. To resolve this, ensure the input in ‘start_date’ is a correct date value.
Best Practices
Always ensure to have the correct weekend string to avoid inaccuracies. It’s also good to cross-verify the holidays and weekend parameters to get the correct project end date.
List of Related Functions
WORKDAY: Calculates the end date given a start date and duration, considering Saturday and Sunday as weekends.EDATE: Returns the date that is a specified number of months before or after a specified date.EOMONTH: Returns the date in the same month, but with the day part adjusted to the last day of the month.NETWORKDAYS: Calculates the number of workdays between two dates, considering Saturday and Sunday as weekends.NETWORKDAYS.INTL: Similar to NETWORKDAYS, but with customizable weekend parameters.
Frequently Used with the Formulas
WORKDAY.INTL is frequently used with other date-related formulas such as DATE, DAY, MONTH, and YEAR.
Frequently Asked Questions
Q. What happens if the ‘days’ parameter is a decimal?
If ‘days’ is a decimal, Excel rounds it down to the nearest whole number.
Q. Can WORKDAY.INTL handle negative values for ‘days’?
Yes, WORKDAY.INTL can calculate a start date when given an end date and a negative value for ‘days’.
Q. How does WORKDAY.INTL handle time values?
WORKDAY.INTL only considers the date part of a datetime value. The time part is ignored.
Master the WORKDAY.INTL formula to enhance your Excel data analysis skills.
Visit our YouTube channel to learn step-by-step video tutorials