Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2021, 2019, 2016 and Mac versions more…

Excel formula to get working days between two dates excluding holidays

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.

Share this article

Recent Articles

excel-sequence-function-tutorial

Excel SEQUENCE Function

This tutorial uses a single Excel SEQUENCE function to create a perpetual calendar. Includes sample files and a step-by-step guide.

Calculating loan payment dates

Calculating Loan Payment Dates

This article shows how to calculate loan payment dates in Excel, using a formula that can calculate loans of any length and number of periods.

Leave a Reply

Your email address will not be published. Required fields are marked *

Which tutorial do you need?