Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Excel NETWORKDAYS.INTL function

By default, weekends are Saturday and Sunday in a week. For calculating the number of working days by excluding weekend days (Saturday and Sunday) or any days that are specified as weekends. , you can try the NETWORKDAYS.INTL function. This tutorial is talking about the formula syntax and usage of NETWORKDAYS.INTL function in Excel.

Description of NETWORKDAYS.INTL function

Syntax of NETWORKDAYS.INTL function

Arguments of syntax

Examples of NETWORKDAYS.INTL function


Description of NETWORKDAYS.INTL function

The Microsoft Excel NETWORKDAYS.INTL function can be used to calculate the number of working days between two dates. By default, it excludes weekends (Saturday and Sunday) from the working days. Besides, you can specify the weekends to be any days other than Saturday and Sunday.


Syntax of NETWORKDAYS.INTL function

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


Arguments of syntax

  • Start_date: The start date of the date range;
  • End_date: The end date of the date range;
  • Weekend: This is an optional argument. You can choose a weekend number to specify which days of the week are considered as weekends, or ignore this argument to take Saturday and Sunday as weekends by default.
  • Holidays: This is an optional argument. A list of dates that is considered non-work days.

The below table displays the weekend numbers and their corresponding weekend days.

 Weekend Number
 Weekend Days
 1 or omitted  Saturday and Sunday
 2  Sunday and Monday
 3  Monday and Tuesday
 4  Tuesday and Wednesday
 5  Wednesday and Thursday
 6  Thursday and Friday
 7  Friday and Saturday
 11  Sunday only
 12  Monday only
 13  Tuesday only
 14  Wednesday only
 15  Thursday only
 16  Friday only
 17  Saturday only

Notes:

1. You can also use weekend string values to represent weekend days and workdays in a week.

2. Weekend string values are consisted of 7 characters which only contains the number 0 and 1. It starts on Monday and ends with Sunday. Number 1 in the string represents a weekend day, and number 0 represents a working day.

For example:

0000100 means that only Friday is taken as weekend day in the week;

0011000 means that Wednesday and Thursday are considered as weekends in the week.

The string “111111” is invalid and always return 0.

1) Directly reference to cells containing start date, end date and dates of holidays: =NETWORKDAYS.INTL( B3, C3,1,F3:F4 ).

2) Directly enter dates as text string: =NETWORKDAYS.INTL("12/20/2018", "1/10/2019",1,{"12/25/2018","1/1/2019"}).


Examples of NETWORKDAYS.INTL function

This section will show you examples of how to calculate working days between two given days with the NETWORKDAYS.INTL function in Excel.

Example 1: Calculate working days between two dates by excluding default weekends

As below screenshot shown, range B3:B5 contains the start dates, and range C3:C5 contains the end dates. For calculating working days between the start and end dates and automatically excluding weekends by default, please do as follows.

1. Select the cell you will display the total working days, enter formula =NETWORKDAYS.INTL(B3, C3) into the Formula Bar and press the Enter key.

2. Then drag the Fill Handle down to apply the formula to other cells. See screenshot:

Example 2: Calculate working days between two dates by excluding specified dates as weekend days

You can specify certain dates (such as Monday and Tuesday) as weekends other than the default Saturday and Sunday, and exclude them from the working days.

1. Select the cell you will display the total working days, enter formula =NETWORKDAYS.INTL(B3, C3,3) into the Formula Bar and press the Enter key.

Or apply this formula =NETWORKDAYS.INTL(B3, C3,"1100000")

2. Keep selecting the result cell, drag the Fill Handle down to apply the formula to other cells. See screenshot:

Example 3: Calculate working days between two dates by excluding specified dates as weekends and holidays

If you want to exclude specified weekends (such as Wednesday and Thursday) and holidays at the same time from the working days with the NETWORKINGDAYS.INTL function, please do as follows.

1. Click on the cell in which you want to display the total working days, enter formula =NETWORKDAYS.INTL(B3, C3,5,F3:F4) into the Formula Bar and press the Enter key.

Or apply this formula =NETWORKDAYS.INTL(B3, C3,"0011000",F3:F4).

Notes:

1). The weekend number “3” and weekend string value “0011000” in the above two formulas mean that Wednesday and Thursday are considered as weekends in the week.

2). F3:F4 is the list of holidays you will exclude from the working days.

2. Drag the Fill Handle down to get all results as below screenshot shown.

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.