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:


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:


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:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


Comments for this tip:

Princes    17 Mar 2015, 03:13
Hi. Please help me with this:

Company A service hour:
Mondays-Saturdays, 7am-5pm
Company B service hour:
Mondays-Friday, 8am-5pm

SLA of Ticket per Priority:
Priority 1 - 4hours
Priority 2 - 8 hours
Priority 3 - 36 hours
Priority 4 - 72 hours

What will be the general formula when a ticket is logged within and beyond the service period per priority?

Thanks in advance for the help!
Vividon80    15 Mar 2015, 06:55

I need help.

I have a formula in Excel that calculates each day someone is off sick from work and stops counting once you enter a return to work date.

However, I need the formula to discount weekends.

Now I'm aware of the "=NETWORKDAYS.INTL..." function.

The current formula I have enables me to calculate the number of days someone is off sick and effectively stops the clock once you enter a return to work date.

So what formula/function calculates the number of days someone is off sick, stops the clock once you enter a return to work date but also excludes weekends?

Kind Regards
DanSquad    14 Mar 2015, 13:41
Hi. Excel master. I have a problem here.,

I want to know the 2 dates i Inputed.

This is the situation

I have 2 dates, the received date and the dispatch date.

I can subtract the two dates but what i want to know is " If no input dates or blank on the cell = the present date.

I hope someone can help me.

EG    06 Mar 2015, 12:29
I found it - i had to add the -1 to the end of the function.
EG    06 Mar 2015, 11:34
I need to calculate the difference between business days.
Cell A1 = 3/3/15
Cell B1 = 3/3/15

Cell C1 then populates with "1". When in reality it should be "0".

What is the function I can use to calculate the difference between business days?
flava    25 Feb 2015, 20:12
Dear All,

I hope if someone here can help me with a formula where I want to get how many days it is due. I want to subtract due dates with the current day and keeps on updating or incrementing with 1 on the next day. And, using Excel 2013

Thanks to all who can help me with this in advance :)

Michael (Micky) Avidan    19 Feb 2015, 09:03
Assuming business days are Mon-Fri and your Excel version is earlier than 2010 and the 1st. Feb is in cells A1 and the 18th. Feb. in Cell B1, - try:
*** For this Function to work in old versions of Excel - you need to enable the "Analysis ToolPak" Add-In.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
jc    18 Feb 2015, 14:02
How do I get excel to give me the actual number of business days (for today it should give me 13 as a result, today = 2/18/15) Thank you
Michael (Micky) Avidan    09 Jan 2015, 05:21
In 'Excel 2003' (and maybe even in 2000/2002) it works fine for me with all its arguments.
If you can't "standarditied" it - try
to enable the Analysis Toolpak add-ins.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
Willy Vanhaelen    08 Jan 2015, 10:36
You can solve this with the WORKDAY function but it is only standard starting Excel 2007
Murtala    07 Jan 2015, 05:24
Thank you very much (Micky) Avidan,

However, my logic is I want employees to input their Leave start date in one column, then I want Excel to calculate 30 workdays from the start date and output the end date of their leave in the next column automatically.

Please see if you can help me out. I have been trying and experimenting with the DATE functions but cqant get the solution :(
Michael (Micky) Avidan    07 Jan 2015, 05:06
The function: WORKDAY has a special argument called: Holidays
Check it out in Excel's help.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
Murtala    07 Jan 2015, 04:36




Michael (Micky) Avidan    06 Nov 2014, 06:55
Assuming today's date resides in cell A1 - Try:
=WORKDAY(A1, 30)
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
Coleen    05 Nov 2014, 16:43
I want a quick formula that can put in today's date and calculate what date it will be for business days. For example, I have a note that was done on 10/7/14 and I need to find out how to calculate what date it will be in 30 business days. I do not have a two date span. I want to find out what date it "will" be once I do the calculation. Thank you
Michael (Micky) Avidan    11 Oct 2014, 12:23
Check Excels Help for both functions:
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
Erica    10 Oct 2014, 12:02
How do I calculate a due date that is 30 business days from the end date? Excluding holidays.
Michael (Micky) Avidan    23 Apr 2014, 12:27
Assuming the start date is in cell A1 and the number of days to add in B1 - try:
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
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
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)
Michael (Micky) Avidan    10 Apr 2014, 16:17
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)

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.


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

Michael Avidan
“Microsoft®” MVP – Excel
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

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:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 4+5 (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


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)



Home Improvement

Money and Finances


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 © 2015 Sharon Parq Associates, Inc.