How do I calculate business days excluding holidays in Excel?

Excel formula to get working days between two dates excluding holidaysIn the world of business its a common requirement to find out how many working days there are between two date

How do I calculate business days excluding holidays in Excel?

Excel formula to get working days between two dates excluding holidays

In the world of business its 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 wont 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 doesnt 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.WorkingDays.xlsx

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.Full Book Details

Some of the things you will learn

Download this book today

Download this book today

Download this book today

Download this book today

Download this book today

Share this articleShare on facebookShare on twitterShare on linkedinShare on stumbleuponShare on email

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.

An Excel formula to get the week of the month

An Excel formula to get the week of the month

This article shows you how to create an Excel formula to get the week of month from a date, as well as how to get the week of year from a date.

Leave a Reply Cancel reply

Your email address will not be published.

Comment

Name

Email

Video liên quan