Summary
The Excel WORKDAY.INTL function computes a workday a specific number of days away from a start date, allowing for customized definition of weekends and optional exclusion of holidays. It is commonly used for calculating due dates, delivery dates, or completion dates, taking into account both working and non-working days.
Syntax
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
- start_date: The start date from which the calculation begins.
- days: Number of working days to add (positive) or subtract (negative) from the start date.
- weekend: [Optional] A numerical or string value defining weekends.
- holidays: [Optional] Range of dates to be treated as non-working days.
Return value
A serial number representing the calculated workday in Excel.
How to use
WORKDAY.INTL is employed to find future or past working days considering predefined non-working days. It offers flexibility in defining weekends through standard numerical codes or a custom 7-character string, with each character representing a day of the week starting from Monday.
Weekend Modes:
- Weekend Modes (Numbers 1-17):
1: Default, Saturday-Sunday.2: Sunday-Monday.3: Monday-Tuesday.4: Tuesday-Wednesday.5: Wednesday-Thursday.6: Thursday-Friday.7: Friday-Saturday.11: Sunday only.12: Monday only.13: Tuesday only.14: Wednesday only.15: Thursday only.16: Friday only.17: Saturday only.
- Custom Weekend Patterns (7-Digit String):
- The string consists of 7 digits, each representing a day of the week starting with Monday.
1represents a weekend,0a working day.- Example:
"0010001"implies Wednesday and Sunday are weekends.
Examples
WORKDAY.INTL with Default Weekend
Calculating a Future Workday (Standard Weekends):
=WORKDAY.INTL("2021-01-01", 10)
Determines the date 10 workdays from January 1, 2021, considering Saturday and Sunday as standard weekends.
WORKDAY.INTL with Custom Weekend Number Code
Calculating a Date Considering Specific Weekends:
=WORKDAY.INTL("2021-01-01", 10, 7)
Calculates the date 10 workdays from January 1, 2021, with Friday and Saturday as weekends.
WORKDAY.INTL with 7-Digit Weekend Code
Determining a Past Workday with a Specific 7-Digit Weekend Code:
=WORKDAY.INTL("2021-01-15", -5, "0101011")
Calculates 5 workdays before January 15, 2021, considering only Monday, Wednesday, and Friday as working days, with the weekend code “0101011” (where Tuesday, Thursday, Saturday, and Sunday are weekends).
WORKDAY.INTL with 7-Digit Weekend Code and Holidays
Calculating a Date with a 7-Digit Weekend Code and Holidays:
=WORKDAY.INTL("2021-01-01", 10, "1010101", A2:A5)
Finds the date 10 workdays from January 1, 2021, considering Tuesday, Thursday, and Saturday as working days (weekend code “1010101”), and taking into account holidays listed in the range A2:A5.
Additional Notes
- WORKDAY.INTL does not count the start date as a working day.
- This function is particularly useful in project management and HR processes where working days need to be calculated accurately.
- It returns a #VALUE! error if the weekend or holidays parameters are invalid.
- The function is versatile in accommodating various working week structures through its customizable weekend parameters.