Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel. This allows you to exclude all weekends, holidays, and any other dates that you specify.
This is an invaluable method to use when calculating payment schedules, shipping dates, and much more in Excel.
To do this we will use the NETWORKDAYS() function.
Syntax
=NETWORKDAYS(start_date, end_date, [holidays])
Argument | Description |
---|---|
Start_date |
The start of the calculation. This must be entered as a date, but, in almost all cases you will simply reference a cell that already contains a date. |
End_date |
The end of the calculation. This must be entered as a date, but, in almost all cases you will simply reference a cell that already contains a date. |
[Holidays] | A list of holidays to exclude from the number of working days calculation. This can be entered as an array of date serial numbers but, it's much easier to simply reference a range of cells that contain dates for the holidays, as described below. |
[] means the argument is optional.
Simple Working Days Calculation
Let's find the number of working days between January 11, 2016 and March 15, 2016.
- Type =NETWORKDAYS( in the cell where you want to see the result.
- Select the starting date for the calculation.
- Type a comma to go to the next argument and then select the ending date for the calculation.
- Hit Enter and you are done.
This tells us that there are 47 working days between these two dates. It is as simple as that.
Working Days Calculation with Holidays
You may need to account for holidays in your company when calculating the total number of working days. This is easy to do and just requires us to maintain a list of holidays that we can then reference.
Let's say that we still want to find out the total number of working days between January 11, 2016 and March 15, 2016, but now we will include some holidays in there.
- Create a list of holidays. I will do this on the same worksheet so you can see everything at once but, in practice, it's best to keep this on a separate worksheet so it doesn't get messed-up.
- Type =NETWORKDAYS( into the desired cell.
- Select the starting date.
- Type a comma and then select the ending date.
- Type another comma to go to the final argument Holidays. Then select the range of dates for your holidays.
- Hit enter and you're done!
You can see now that the total number of working days is three days less once we have included the dates for the holidays.
Notes
If the start_date for the NETWORKDAYS function is on a working day, it will be included in the total number of working days returned by the function; this is also true for the end_date.
This is a very helpful function to use in Excel for keeping track of work, employees, billing, and so much more. It's very simple to use but you must make sure that your holiday dates list is properly updated every year so that you don't have issues with the final calculation.
Make sure to download the accompanying spreadsheet so you can see this function in action.
Question? Ask it in our Excel Forum
Tutorial: I have found a rather convoluted method to Calculate the Number of Years, Months and Days ...
Tutorial: Here, youll learn how to get the difference between two times in Excel. A common example...
Tutorial: How to get the name of a day from a date in Excel. This returns, for example, "Tuesday" fo...
Tutorial: Use a function in Excel to get the number of the day in a week, from 1 to 7. This allow...
Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...
Tutorial: How to find a date that is so many days before or after a specified date, excluding weeken...