Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Calculating Business Days

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Calculating Business Days.

In performing calculations with Excel, it is often helpful to know how many days there are between two dates. Excel makes this easy—you just subtract the earlier date from the latter.

In a business environment, however, you may not want to know just the number of days—you probably want to know the number of business days between two dates. In other words, how many workdays are there between two dates?

Believe it or not, Excel makes it almost as easy to calculate business days as it is to calculate regular days. All you need to do is use the NETWORKDAYS worksheet function. This function is not intrinsic to Excel; it is part of the Analysis ToolPak. (How you enable the Analysis ToolPak is discussed in other ExcelTips.)

Let's suppose for a moment that you had two dates: one in A3 and the other in A4. The date in A3 is your starting date and the date in A4 is the ending date. To calculate the work days between the two dates, you could use the following formula:

=NETWORKDAYS(A3,A4)

This returns a count of all the days between the two dates, not counting weekends. You should note that the function returns the number of full days. Thus, if your starting date was Sept. 4 and your ending date was Sept. 5, the function would return a value of 2. (Provided neither day was a weekend day.)

If you want to account for holidays, the easiest way is to enter your standard holidays in a range of cells, and then define a name for that range. (I always like the terribly obvious name of "Holidays.") You can then alter the NETWORKDAYS formula in this manner:

=NETWORKDAYS(A3,A4,Holidays)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2155) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Calculating Business Days.

Related Tips:

Step Up and Take Control! Subscribers to ExcelTips know just how valuable a resource it is. ExcelTips Premium provides twice the number of exceptional, easy-to-understand tips every week in an ad-free newsletter, as well as substantial discounts on ExcelTips archives and e-books. Check out ExcelTips Premium today!

 

Comments for this tip:

Michael (Micky) Avidan    23 Apr 2014, 12:27
@Patti,
Assuming the start date is in cell A1 and the number of days to add in B1 - try:
=WORKDAY(A1,B1)
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
Patti    22 Apr 2014, 10:54
If I have a start date and the duration is 2 days (indicated by a whole number is an adjacent cell), what formula do I use to find the end date which COUNTS the start date as one day, excluding holidays and weekends. This is for creating a schedule, and want to be able to change the "Duration" whole number and have it automatically calculate the new end date. Or have a start date and calculate the start date for the next task based on the days from previous task start date including that start date as Day 1.
Michael (Micky) Avidan    10 Apr 2014, 16:19
@CG,
Put: =TODAY() in the starting date cell or: TODAY() directly into the formula.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
Michael (Micky) Avidan    10 Apr 2014, 16:17
@JKP,
if the "aware" date is in cell A1 and the amount of workdays to be added is in cell A2 -
type the following formula into cell A3: =WORKDAY(A1,A2)
and you should end up with the: March, 14, 2014.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL

CG    09 Apr 2014, 14:28
How can I set up the formula to always look at today's date?
JKP    13 Mar 2014, 12:16
I am trying to add 30 business days to an "aware" date (in this case it's Jan 31 2014) to calucale a "due date". can you help me with the calculation?
Michael (Micky) Avidan    20 Feb 2014, 08:56
Although this tip is intended for "97-2007" users - all "Modern" users should be aware that starting with "Excel 2010" there is an extended NetWorkdDays Function: NETWORKDAYS.INTL

The following formula calculates the working days' between two given dates, excluding Fri-Sat.

=NETWORKDAYS.INTL(A3,A4,7)

One should check "Excel" Help for, so called, excluded "weekends" options.

Michael Avidan
“Microsoft®” MVP – Excel
ISRAEL
Doug Roach    19 Feb 2014, 11:46
I had a challenge because of a business process wrinkle. They wanted monthly SLA averages of ticket times, but only wanted to charge the help desk for time spent between 7:00am and 5:00pm.

The most direct solution I could devise was to use NETWORKDAYS to add the time for the first day, the time of the last day, and 10 x the count of the days in between.

NETWORKDAYS made it easy to eliminate weekends and holidays, but I had to devise error checking in the formula and limit one day responses to not include additional time.
PhilP    07 Feb 2013, 12:57
Sara,

Add -1 to the end of the formula thus: =NETWORKDAYS(A3,A4)-1
Sara Talluto    06 Feb 2013, 15:11
You said this function counts the number of full days "Thus, if your starting date was Sept. 4 and your ending date was Sept. 5, the function would return a value of 2"

Is there a function to use that would not count the start date as a day?

I work at a test lab, and I have to calculate how many days tests were started late. So if the start date was Sept. 4 and the end date was Sept. 5, I need to show that the test started one day late. Any advice will be helpful.
TJ    02 Jan 2013, 16:52
If this tip does not work on your spreadsheet, you need to do the following:
Go to: Tools ---> Add Ins, then turn on Anaysis ToolPak.
After the Analysis ToolPak installs, re-type the formula and it should work just fine.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.