WORKDAY.INTL function

1 year ago

5 minutes

The WORKDAY.INTL function in Excel calculates a date that is a specified number of working days before or after a given start date, excluding weekends and optionally any holidays, with the flexibility to define custom weekends (non-working days). This function is particularly useful when your workweek does not follow the default Saturday-Sunday weekend.

Syntax

WORKDAY.INTL(start_date, days, [weekend], [holidays])

Parameters

start_date: The date from which you want to begin calculating. This is a required parameter and can be a date entered directly or a cell reference containing a date.
days: The number of working days to add (positive number) or subtract (negative number) from the start_date. This is a required parameter.
[weekend] (optional): A number or a 7-character string that specifies which days of the week are considered weekends. By default, weekends are Saturday and Sunday, but you can customize this to suit different workweek schedules.
- Number (1 to 7): You can specify the weekend type using a number:
  - 1 = Saturday and Sunday (default)
  - 2 = Sunday and Monday
  - 3 = Monday and Tuesday
  - 4 = Tuesday and Wednesday
  - 5 = Wednesday and Thursday
  - 6 = Thursday and Friday
  - 7 = Friday and Saturday
- 7-character string: This is a string of 7 digits (0 or 1) where each digit represents a day of the week, starting from Monday. A “1” represents a non-working day (weekend), and a “0” represents a working day. For example:
  - “0000011”: The weekend is Saturday and Sunday.
  - “1000000”: The weekend is Monday.
  - “1111100”: The weekend is Friday and Saturday.
[holidays] (optional): A range of one or more dates to exclude as holidays. These dates will not be counted as working days. This parameter is optional, and if not provided, the function will only exclude weekends.

How It Works

The WORKDAY.INTL function adds or subtracts the specified number of working days from the start_date, excluding weekends (which can be customized) and any holidays you provide.
The weekend argument allows you to define which days of the week are considered weekends, making it highly flexible for different types of workweek schedules.

Examples

Basic Example (Default Weekends): If A1 contains January 1, 2025 (a Wednesday), and you want to calculate a date 10 working days later with the default weekend (Saturday-Sunday), you can use:
```
=WORKDAY.INTL(A1, 10)
```
This will return a date 10 working days after January 1, 2025, excluding weekends (Saturday and Sunday).
Custom Weekends (Weekend on Friday-Saturday): If your weekend falls on Friday and Saturday (meaning your workweek is Sunday through Thursday), and you want to calculate 10 working days from A1, you can specify the weekend as “1111100”:
```
=WORKDAY.INTL(A1, 10, "1111100")
```
This will return a date 10 working days from A1, excluding Fridays and Saturdays as non-working days.
Subtracting Working Days with Custom Weekend: If you want to subtract 5 working days from A1 (January 1, 2025) with a custom weekend (Friday and Saturday), you can use:
```
=WORKDAY.INTL(A1, -5, "1111100")
```
This will return a date that is 5 working days before A1, considering Friday and Saturday as weekends.
Including Holidays: If you have a list of holidays in B1:B3 (e.g., January 1, 2025, January 26, 2025, and December 25, 2025) and want to calculate a date 10 working days from A1, excluding both weekends and these holidays, you can use:
```
=WORKDAY.INTL(A1, 10, "1111100", B1:B3)
```
This will return a date that is 10 working days from A1, excluding weekends (Friday and Saturday) and the holidays listed in B1:B3.
Weekend on Sunday and Monday: If your workweek starts on Tuesday and ends on Saturday (weekend is Sunday and Monday), and you want to calculate the date 10 working days after A1:
```
=WORKDAY.INTL(A1, 10, 2)
```
This will return a date 10 working days after A1, excluding Sundays and Mondays as weekends.

Common Use Cases

Custom Workweek: If your company operates on a different schedule (e.g., Sunday to Thursday workweek), the WORKDAY.INTL function lets you define weekends and calculate working days accurately.
Project Management: When managing projects, especially those involving different countries with different holidays and workweek schedules, WORKDAY.INTL can be used to calculate deadlines and milestones while respecting custom weekends and holidays.
Payroll and HR: The function is useful for calculating pay periods or work hours for employees who work on non-standard schedules.

Important Notes

The WORKDAY.INTL function provides more flexibility than the standard WORKDAY function, as it allows for customization of the weekend days.
The holidays argument is optional. If you have a list of holidays that should be excluded from the working day count, you can provide it as a range or array.
The weekend argument can be customized using either a number (1 to 7) or a 7-character string. This provides flexibility to handle various workweek configurations around the world.
This function does not account for partial workdays. It only counts full working days.

Summary

The WORKDAY.INTL function in Excel is an enhanced version of the WORKDAY function that allows you to customize weekends to match non-standard workweek schedules. It calculates a date that is a specific number of working days before or after a given start date, excluding both weekends and any specified holidays. This flexibility makes it ideal for businesses with unique work schedules or international operations.

Post Views: 26