When to use the Networkdays Function
How many days an employee has worked, and should get paid for, has always been a point of concern for an organization. The department responsible for implementing this validation is typically the Payroll department along with Human Resources and other Accounting personnel that prepare the monthly payroll file/report for the company.
This important task could be done manually, as has been the practice for decades, but now spreadsheets can provide a more accurate, less tedious, and faster solution to this problem. In today’s post we will learn how to setup and use the Networkdays and Networkdays.Intl formulas in Excel to automate this task with greater accuracy.
How to use the Networkdays and Networkdays.Intl Functions:
Before we start this tutorial, please download the companion workbook so that it is easier for you to follow the instructions.
MS Excel (2010) has two functions that are mostly used for this task:
1. NETWORKDAYS()
2. NETWORKDAYS.INTL()
We will learn how to use this syntax and setup a sample sheet for our company.
1. NETWORKDAYS() – Networkdays simply counts the number of days between two dates excluding the weekends – i.e. Saturday and Sundays and a list of holidays or absences from that range. The syntax of the function is:
=NETWORKDAYS(start_date,end_date,[holidays])
Where:
• Start Date = A value entered as a date (date entered as text will return an error)
• End Date = Enter the date that marks the end of the target period
• Holidays = Enter any holidays taken by the employee – larges braces “[]” mark that this is an optional argument.
In order to demonstrate the setup we have created the following example. See the following picture where Employee’s name, Start Date and End date will be entered.
For example for Mr. Pearson in the following table, the network days can be calculated using the formula:
=NETWORKDAYS(B3,C3,E3:I3)
The formula takes into account the data provided in the following table and returns the corresponding workday count.
The formula will automatically discount the weekends and we can vacations to the right (again this is optional) – the reason behind adding workdays before vacations is to give more space for vacations.
It is also to be noted that we can add either single cell or a range of cells to the third argument of the formula.
Why Use NETWORKDAYS.INTL() when we have already NETWORKDAYS() at our disposal?
=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])
And the following table of options can be used to set the weekend:
For example in Middle East Region the normal weekly day off falls on a Friday. The number of days a person works between two dates without any holidays will be:
The formula we used here is:
=NETWORKDAYS.INTL(B3,C3,E3,)
The third option for the holidays is omitted as it is optional. If we have some vacations as well, we can add it to the table like we did in the previous table and the formula becomes:
=NETWORKDAYS.INTL(B3,C3,E3,F3:J3)
Other uses of NETWORKDAYS() and NETWORK.INTL() functions:
It is not only Human resources and Accounting that can use this function but it’s also helpful for the Project Management and Customs departments as well. For project management related tasks, it is commonly used to find the number of days that work could be scheduled for and how many days the project team will not be working (weekends, holidays, etc)
Similarly, for the Customs department, it is used to find the time that goods will take to reach the destination considering local and international conditions – weekend settings, holidays and the days when customs and import operations staff will be available. Download the sample file to try it out yourself and please comment if you have other questions about the application or use of this formula.