The NETWORKDAYS.INTL is a Date & Time function that calculates and returns the number of business days between two given dates. The NETWORKDAYS.INTL function allows you to specify which days of the week are weekends and also use a list of holidays. In this guide, we’re going to show you how to use the NETWORKDAYS.INTL function to find the number of business days between two dates, and also go over some tips and error handling methods.
- Excel 2010 and newer versions
NETWORKDAYS.INTL Function Syntax
|start_date||Start date of the period you want to get business days between.|
|end_date||End date of the period you want to get business days between.|
|[weekend]||Optional. A number or string value that indicates which days of the week are the weekends. Please see the table below for available options.|
|[holidays]||Optional. A range of date values to be excluded aside from the weekends. You can also use an array of constants.|
You can use the number codes for the [weekend] argument if you'd like to count weekends as a single day or two consecutive days. See below for more options.
|Weekend number||Weekend days (Non Business Days)|
|1 or omitted||Saturday, Sunday|
Alternatively, you can enter a 7-character string that indicates the business days and weekends. The days start with Monday and you can use zeros and ones (0, 1) which represent a business day and a no work day respectively. For example, string 1000001 indicates that Mondays and Sundays are non-workdays.
Finding the Number of Business Days Between Two Dates
Only weekends (Saturday and Sunday) excluded
Saturdays and Sundays are weekends by default. To find the number of business days excluding only the default weekend days, simply enter a value for the start_date and end_date arguments. Both date values can be a string, a serial number, or a calculated date by a formula (e.g. DATE). Here is an example:
Custom weekends and holidays excluded
Custom weekend (non-working) days can be specified by entering [weekend] and [holidays] arguments.
Use one of the predefined numbers or a string code to specify which days are to be excluded as weekends.
- INTL(DATE(2019,10,1),DATE(2019,11,30),5) assumes that Wednesday and Thursday are weekend days.
- INTL(DATE(2019,10,1),DATE(2019,11,30),"0101010") excludes Tuesday, Thursday, and Saturday.
You can provide an array of dates for the [holidays] arguments to exclude specific dates in addition to the weekends. The [holiday] argument can be a range of cells or a constant array.
- Use the NETWORKDAYS function if you are using Saturday and Sunday for no business days.
- NETWORKDAYS.INTL function ignores time value of dates.
- Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
- If text arguments are not valid dates, the INTL function returns #VALUE! error value. Instead, use the DATE function.