In the world of business it’s a common requirement to find out how many working days there are between two dates. This calculation can be useful for payroll, project management and planning of all kinds.
Excel makes these calculations easy with the NETWORKDAYS function.
A simple NETWORKDAYS formula will get the number of week days between two dates, such as:
=NETWORKDAYS(A2, B2)
…but this won’t account for holidays.
To make sure that holidays are excluded, you will first need to enter the dates that you want to be recognized as holidays. Excel doesn’t maintain its own list of national holidays, since they vary all over the world. I have used this table of UK bank holidays:
Once you have a list of holidays, you can expand your NETWORKDAYS formula to automatically exclude them, with something like:
=NETWORKDAYS(A2,B2,B4:B11)
(where B6:B13 contains your list of holidays).
You can find much more information about how to work with dates in Excel in our Essential Skills and Expert Skills courses.
Example workbook
You can download an example workbook showing the formula in action.
Similar functions
WORKDAY
The WORKDAY function works in the same way as NETWORKDAYS, but instead of returning the number of days between two dates it returns the end date after a certain number of working days. You could use this function to find out the end date of a project that was scheduled to take 20 working days, taking weekends and holidays into account.
NETWORKDAYS.INTL and WORKDAY.INTL
The standard WORKDAY and NETWORKDAYS functions always consider Saturday and Sunday to be weekends and will never consider them to be working days. Many companies remain open over the weekend and are closed on different days of the week, making WORKDAY and NETWORKDAYS useless.
Realizing this, Microsoft added the INTL versions of the two functions. These include an additional Weekend parameter that allows you to specify which days of the week should be treated as weekends.
NETWORKDAYS.INTL and WORKDAY.INTL have been available in every version of Excel since Excel 2010. If you are using Excel 2007 or earlier you will need to upgrade to a more recent version to use these functions.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Some of the things you will learn
