Contents of Table
Excel NETWORKDAYS formula – If you work in payroll or the like and are tasked with paying employees’ salaries which are calculated based on the number of days the employees work, then you need to read this excel class tutorial this time.
Effective working days are the number of working days within a certain date range, ignoring weekends and/or days when employees are absent from work, for example due to leave, permits or national holidays.
In Microsoft Excel, we can calculate the number of effective working days with the NETWORKDAYS function or formula , which is an excel function in the date and time category that is used to calculate the number of days between two dates other than weekends and certain dates specified as holidays or holidays. not weekdays.
If you are using Microsoft Excel 2010 and above, for more detailed weekend parameters, you can also use the NETWORKDAYS.INTL function which has the same function.
For more details on how to use the NETWORKDAYS and NETWORKDAYS.INTL functions to calculate the number of effective working days, please refer to the following excel tutorial.
EXCEL NETWORKDAYS FUNCTION
In Microsoft Excel the Networkdays function is used to calculate the number of days between two dates that do not include weekends ( Saturday and Sunday ) as well as other dates specified as working holidays.
In the Networkdays formula, weekends are Saturdays and Sundays . If you want to specify a weekend as another day use the Networkdays.Intl function which is discussed in the next section.
How to Use Networkdays Function
The way of writing or the syntax of the Networkdays function in the excel formula is as follows:
NETWORKDAYS(Start_Date; End_Date, [Holiday_Day])Information
- Start_Date
Filled with work start date to be calculated.
- End_Date
Filled with work completion date to be calculated.
- [Holiday]
is a data range or array constant containing a date value that indicates the serial number of the date to be excluded from the effective working day. This argument is optional so it can be left blank if there are no work holidays that will be ignored.
Example of Networkdays Formula
Next, please consider the following example of using the Networkdays formula:
Code:
=NETWORKDAYS(C2;D2)
In this first formula the number of working days between June 1, 2017 to September 30, 2017 is 87 effective working days. This figure is obtained by subtracting the number of days in that range (122 Days) by the number of weekend days (Saturday and Sunday) in that date range which amounts to 35 Days.
Code:
=NETWORKDAYS(C4;D4;A2:A8)
In the 3rd formula, in addition to subtracting the number of days between 2 dates by the number of Saturdays and Sundays, the number of days is also reduced by the number of dates in the range A2:A8 that are included between the two start and end dates.
In the Networkdays function which is calculated as weekends excluded on weekdays effective only Saturdays and Sundays, what if we want weekends here to be set as Sundays only? or Sunday and Monday for example.
For this requirement starting from the 2010 version of excel, excel added a new excel function namely the NETWORKDAYS.INTL function which we will discuss in the next section below.
EXCEL NETWORKDAYS.INTL FUNCTION
The Networkdays.Intl formula has the same function as Networkdays in that it calculates the number of effective working days between two dates. The difference is that on Networkdays weekends always fall on Saturdays and Sundays, on Networkdays.Intl we can set weekend parameters according to our needs.
The NETWORKDAYS.INTL function is only available for Microsoft Excel version 2010 and above.
How to Use the NetworkDays.Intl Function
The parameters or arguments of the NetworkDays.Intl function are as follows:
Code:
NETWORKDAYS.INTL(Start_Date; End_Date; [Week_End]; [Holiday_Day])
Information
- Start_Date
Filled with work start date to be calculated
- End_Date
Filled with work completion date to be calculated.
- [Weekend]
Filled with the weekend code that we want to ignore from weekdays.
- [Holiday]
is a data range or array constant that contains one or more serial numbers of work holiday dates. This argument is optional so it can be left blank if there are no work holidays that will be ignored.
In the Networkdays.Intl function, the Weekend argument is also optional or can be left blank. If not filled then this function will work like the Networkdays function which considers Saturdays and Sundays as weekends.
We can fill this weekend parameter with a code number 1-17 which indicates on what days the weekend is applied or when the worker we count does not come to work.
| CODE | WEEKEND |
| 1 | Saturday Sunday |
| 2 | Sunday Monday |
| 3 | Monday Tuesday |
| 4 | Tuesday Wednesday |
| 5 | Wednesday Thursday |
| 6 | Thursday Friday |
| 7 | Friday Saturday |
| 11 | Sunday |
| 12 | Monday |
| 13 | Tuesday |
| 14 | Wednesday |
| 15 | Thursday |
| 16 | Friday |
| 17 | Saturday |
| Code Weekend Function Networkdays.Intl |
In addition to using the weekend code above, we can also set the weekend parameter with a seven character text string in the form of a combination of the text numbers 1 and 0 representing Monday-Sunday. The number 1 represents holidays while the number 0 represents work days.
For example for weekends or holidays Saturday and Sunday the string code used is 0000011 . As for those who only work on Mondays, Tuesdays and Thursdays ( Wednesday, Friday, Saturday and Sunday off ) then the String code is 0010111 .
Example Networkdays.Intl Formula
To make it clearer, let’s look at an example of using the following Networkdays.Intl formula:
Code:
=NETWORKDAYS.INTL(C2;D2)
In this first formula, the Networkdays.Intl function calculates the number of effective working days between August 1, 2017 to September 30, 2017 by assuming Saturday and Sunday as weekends where workers are not actively working.
Code:
=NETWORKDAYS.INTL(C2;D2;11)
In the second formula parameter 11 specifies weekend days as Sundays only.
Code:
=NETWORKDAYS.INTL(C2;D2;”0000011″)
For the above formula weekdays are calculated excluding Saturday and Sunday by using the text string code parameter
Code:
=NETWORKDAYS.INTL(C2;D2;11;A2:A4)
For this formula, the effective working days are calculated based on the range of August 1, 2017 to September 30, 2017 and subtracting it by the number of Sundays in that range and the number of holidays specified in the range A2:A4.
As explained above that the Networkdays and Networkdays.Intl functions in Microsoft Excel are used to calculate the number of working days, then what if what we want to get is the end date of the work?
We will continue this discussion in the next tutorial.
Before leaving this page, don’t forget to share this excel tutorial, who knows one of your friends is also in need of it.
Stanley Sanchez is a freelance writer, editor, and blogger for hire. He has 8 years of experience in copywriting and editing, with a focus on web content development, SEO promotions, social media marketing, and the production of blogs. He specializes in teaching blog writers how to express their stories through words. In his spare time, he enjoys reading about science and technology.
