How to Use the NETWORKDAYS.INTL Function in Excel
In this article, we will delve into the NETWORKDAYS.INTL function, a versatile tool in Microsoft Excel and Google Sheets designed to compute the number of working days between two specific dates. This function is particularly useful as it allows for the exclusion of weekends (which can be customized) and any designated holidays. We will provide a detailed overview of how to effectively apply this function in various scenarios.
Basic Syntax
The basic syntax for the NETWORKDAYS.INTL function is as follows:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])- start_date: The beginning date of the specified period.
- end_date: The ending date of the specified period.
- weekend: A code that identifies which days should be recognized as the weekend.
- holidays: An optional range listing specific dates that should be omitted from the working day count.
Calculating Workdays
First, let’s consider a basic example that utilizes the NETWORKDAYS.INTL function to determine the number of working days between two dates.
| Start Date | End Date | Result |
|---|---|---|
| 2022-10-01 | 2022-10-10 | =NETWORKDAYS.INTL(A2, B2) |
In this example, the formula calculates the working days from October 1, 2022, to October 10, 2022, by default excluding the weekend days, Saturday and Sunday.
Specifying Custom Weekend Days
The function also allows for customization of which days are treated as weekends. For instance, if you need Fridays and Saturdays to be considered as weekends, you can modify the weekend parameter:
| Start Date | End Date | Custom Weekends | Result |
|---|---|---|---|
| 2022-11-01 | 2022-11-10 | 15 | =NETWORKDAYS.INTL(A6, B6, C6) |
Here, the formula computes the working days between November 1, 2022, and November 10, 2022, with Fridays and Saturdays defined as weekend days.
Excluding Holidays
Additionally, the function can exclude holidays from the working day count. You can specify the dates you wish to exclude by using the holidays parameter:
| Start Date | End Date | Holidays | Result |
|---|---|---|---|
| 2022-12-01 | 2022-12-10 | 2022-12-05, 2022-12-08 | =NETWORKDAYS.INTL(A10, B10, 1, D10:D11) |
In this scenario, the formula calculates the working days between December 1, 2022, and December 10, 2022, explicitly excluding December 5 and December 8, 2022, as holidays.
With the customization options provided by the NETWORKDAYS.INTL function for weekends and holidays, you can accurately calculate the number of working days for a given period to suit your specific needs in both Excel and Google Sheets.
More information: https://support.microsoft.com/en-us/office/networkdays-intl-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28