how to use excel networkdays.intl function

Quick Intro

NETWORKDAYS.INTL can help you to calculate working days between two dates. And, the best part is you can specify days to take for a weekend (any of days in a week).

In simple words, it will return the number working days between two dates by excluding weekends and holidays. Let’s say if you want to specify Mon and Tue as a weekend, you can do that using in NETWORKDAYS.INTL. And, you can also specify a particular day as a holiday.

Syntax

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

  • start_date: A valid date from where you want to calculation.
  • end_date: A valid date up to which you want to calculate.
  • weekend: Weekend days you want to exclude from the calculation (a number represent the days to exclude).

weekend argument in excel networkdays.intl function

  • holidays: Dates which you want to exclude from the calculation.

More Information on Excel NETWORKDAYS.INTL Function

  • If a start date is greater than ending date, it will return days in negative.
  • If start date or end date is a date which is out of the range, it will return a #NUM!.
  • You can enter a date using following methods.
    1. Referring to a cell which contains the date value.
    2. Inserting a date directly into the function by using double quotation marks.
    3. Inserting a serial number which represents a date as per excel’s date system.

Examples

Here I have some examples which will help you to understand this function in a better way.

Example- 1

In below example, I have used 01-Jan-2015 as a start date and 20-Jan-2015 as an end date. I have specified 1 to take Sunday – Saturday as a weekend.

Example For Excel NETWORKDAYS.INTL Function Without Holidays

The function has returned 14 days after excluding 6 weekend days.

Example – 2

Below, I have used same dates. And, I have used 11 in for weekend days which means it will only consider Sunday as a weekend. Along with that, I have also used 10-Jan-2015 as a holiday.

Example For Excel NETWORKDAYS.INTL Function With Holidays

Now we have total 3 Sundays between both dates & a holiday. After excluding all these days the function has returned 16 days in the result.

Example – 3

In below example, I have used range to specify holidays. If you have more than one date for holidays you can refer to an entire range.

Example For Excel NETWORKDAYS.INTL Function With Multiple Holidays

Quick Tip: If you want to create a dynamic range for holidays, you can use a table for that.

Example – 4

If you want to choose custom days to count as working days ora weekend, you can use below format in the weekend argument.

Example For Excel NETWORKDAYS.INTL Function With Non Consective Days 

Here, 0 represents a working day and 1 represents a non-working day. And, total seven numbers represent 7 days of a week.

You can use 0 for the days want to take as a working day and 1 for weekends. Here I have specified Monday and Thursday as working days.

Sample File

download sample file to learn more about this tips

What’s Next?

To learn more about Excel NETWORKDAYS.INTL Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

There is also you have NETWORKDAYS function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.



  • FreddyA Briceno

    I think that dateif is the most important one.