Excel Formula – Calculate Number of Working Days
This function is different from just Calculating the difference between Two dates.
Here we will use the function to calculate Working days between two dates – i.e., ignoring the Week end holidays (Saturdays & Sundays) and also national or official holidays.
- Formula Syntax: NetworkDays (Start-date, End-date, [holiday list or Range])
- Function Type: Excel Worksheet Formula
- Returns: Number of calculated Working Days
Lets see how this function works with a simple example.
1. Basics: Calculate Number of Working Days in a Year (2018)
This function is much useful for people in Payroll department. They have to calculate the number of working days for the employee to determine how much the companies money will be spend as Salary for the employees.
So the start date is: 1-Jan-2018. End date is – 31-Dec-2018 . Enter this formula in a new Excel Worksheet.
'Excel worksheet Function - Calculate Number of Working days
'Returns Number of Days between start & end date.
Also excludes Saturdays & Sundays in the calculation.
=NETWORKDAYS("1-Jan-2018","31-Dec-2018")
'OR
=NETWORKDAYS(A1,B1)
You will get the result as 261 days.
Instead of passing date as string parameters, You can enter the date values in a cell & give its reference i,e, = NetworkDays(A1,B1)
2. Calculate Working Days with Custom Holiday List
Please note that the third Parameter in the syntax.
This option enables the user to provide a range of cells that has the list of Holidays that has to be excluded while calculating working days.
=NETWORKDAYS("1-Jan-2018","31-Dec-2018","1-May-2018")
'OR
=NETWORKDAYS(A1,B1,A2:A10)
This is how a user can give a custom Holiday list.
3. Advanced: Custom Weekends for Working Days Calculations
What if your company’s weekend holiday falls on Sunday & Monday?
Excel has option for that also. You need to use a bit advanced function.
- Function Name: NetworkDays.INTL
- Formula Syntax: NetworkDays .INTL(Start-date, End-date, [Weekend Option],[holiday list or Range])
WeekendOptions are:
- 1 or omitted (Saturday, Sunday)
- 2 (Sunday, Monday)
- 3 (Monday, Tuesday)
- 4 (Tuesday, Wednesday)
- 5 (Wednesday, Thursday)
- 6 (Thursday, Friday)
- 7 (Friday, Saturday)
- 11 (Sunday only)
- 12 (Monday only)
- 13 (Tuesday only)
- 14 (Wednesday only)
- 15 (Thursday only)
- 16 (Friday only)
- 17 (Saturday only)
Other than this custom Weekend option the working of this function is the same as its basic version.
External Reference: Excel NETWORKDAYS.INTL function – Microsoft Office Support