NETWORKDAYS.INTL function

1 year ago

5 minutes

The NETWORKDAYS.INTL function in Excel is an advanced version of the NETWORKDAYS function. It allows you to calculate the number of working days between two dates, excluding weekends and optional holidays, but with the added flexibility of defining custom weekend days (instead of just Saturday and Sunday). This is useful for businesses or regions that observe weekends on days other than Saturday and Sunday.

Syntax

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Parameters

start_date: The start date of the period you want to calculate the working days for. This should be a valid date or a reference to a cell containing a date.
end_date: The end date of the period you want to calculate the working days for. This should be a valid date or a reference to a cell containing a date.
[weekend] (optional): A 7-character string or a numeric code representing the days of the week that are considered weekends. This allows you to specify custom weekends (e.g., Friday and Saturday). If omitted, Excel assumes the weekend is Saturday and Sunday (default behavior).
- String format: The string has 7 characters, where each character represents a day of the week starting from Monday. For example, “0000011” means that Saturday and Sunday are weekends, and Monday to Friday are working days.
- Numeric codes: You can also use numeric codes for weekends, where:
  - 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
[holidays] (optional): A range or array of dates that should be excluded from the calculation (e.g., public holidays). These dates will be excluded from the working day count, in addition to weekends. This parameter is optional.

How It Works

The NETWORKDAYS.INTL function calculates the number of weekdays (working days) between two dates, excluding weekends (which can be customized) and optionally excluding specified holidays.
The weekend argument lets you customize which days of the week are considered weekends. By default, Excel assumes weekends are Saturday and Sunday, but you can change this if needed.

Examples

Standard Use with Default Weekends (Saturday and Sunday): To calculate the number of working days between March 1, 2025 and March 10, 2025, excluding the weekends (Saturday and Sunday), use:
```
=NETWORKDAYS.INTL("2025-03-01", "2025-03-10")
```
Result: 6 (March 1 is a Saturday, so the weekends are excluded, leaving Monday through Friday as working days).
Custom Weekend Days (Friday and Saturday): If your weekends are Friday and Saturday, you can specify this using the weekend argument. To calculate the working days between March 1, 2025 and March 10, 2025, where Friday and Saturday are weekends, use:
```
=NETWORKDAYS.INTL("2025-03-01", "2025-03-10", "0000011")
```
Result: 7 (The weekends are Friday and Saturday, so only Sunday to Thursday are considered working days).
Using Numeric Weekend Codes: You can also use numeric codes to define the weekend days. For example, to treat Thursday and Friday as weekends:
```
=NETWORKDAYS.INTL("2025-03-01", "2025-03-10", 6)
```
Result: 6 (With Thursday and Friday as weekends, Monday to Wednesday and Saturday to Sunday are working days).
Excluding Holidays: If you want to exclude specific holidays, such as March 5, 2025, in addition to weekends, you can use the [holidays] parameter:
```
=NETWORKDAYS.INTL("2025-03-01", "2025-03-10", "0000011", {"2025-03-05"})
```
Result: 5 (Excluding weekends and the holiday on March 5, 2025, the working days are March 1, 3, 4, 6, and 7).
Using a Range for Holidays: If the holiday dates are stored in a range (for example, A1:A3 contains the dates 2025-03-05, 2025-03-07, and 2025-03-09), you can reference the range as the [holidays] argument:
```
=NETWORKDAYS.INTL("2025-03-01", "2025-03-10", "0000011", A1:A3)
```

Common Use Cases

Project Management: This function is useful for calculating the number of working days between two dates, especially when custom weekends are observed (e.g., if a business or country has a different weekend schedule).
Payroll Calculations: It is commonly used in payroll systems to calculate the number of actual working days within a given pay period, excluding both weekends and specific holidays.
Business Operations: Businesses operating in regions with different weekend days can use this function to compute deadlines, working hours, or operational periods effectively.

Important Notes

The weekend argument provides great flexibility in defining custom weekend days, and it can either be a string (7 characters, representing the days of the week) or a numeric code.
The [holidays] argument is optional, but it is important to use if there are specific dates (e.g., public holidays) that should not be counted as working days.
If the start_date or end_date is not a valid date or if the [holidays] range contains invalid dates, the NETWORKDAYS.INTL function will return a #VALUE! error.

Summary

The NETWORKDAYS.INTL function is a powerful tool in Excel for calculating the number of working days between two dates, with the flexibility to define custom weekend days. It can exclude weekends, holidays, or both, making it ideal for use in project management, payroll, and other business operations where working day calculations are required.

Post Views: 11