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.
- 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).
- 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.
- Referring to a cell which contains the date value.
- Inserting a date directly into the function by using double quotation marks.
- Inserting a serial number which represents a date as per excel’s date system.
Here I have some examples which will help you to understand this function in a better way.
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.
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.
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.
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.
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.
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.