Formula to Find Number of Weekdays Between Dates

Last reviewed: August 20, 1995
Article ID: Q61575
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a
  • Microsoft Excel for Windows 95, version 7.0

SUMMARY

In Microsoft Excel version 4.0 and later, use the following formula to find the number of weekdays between two dates, where A1 equals the first date first date and A2 equals the second date:

   =NETWORKDAYS(A1,A2)

NOTE: The dates in A1 and A2 must be weekdays, or the result may not be correct.

In all versions of Microsoft Excel including version 4.0 the following formula can be used to get the same result as the NETWORKDAYS function:

   =A2-A1+1-INT((A2-A1+1)/7)*2-IF(INT((A2-A1+1)/7)=(A2-
   A1+1)/7,0,IF(WEEKDAY(A2)<WEEKDAY(A1),2,0))-
   IF(OR(WEEKDAY(A1)=1,WEEKDAY(A2)=7),1,0)

MORE INFORMATION

The first part of the formula determines the total number of days between the two dates.

The next section determines the number or whole weeks between the two dates. This section then multiplies that by two because there is a Saturday and a Sunday in each whole week.

The last section determines if the range spanned a weekend even though it was not a whole week. For example, if your start date was on a Thursday, and your end date was on a Monday, there would be a weekend in between. The WEEKDAY function would return a 2 for Monday and a 5 for Thursday, so an additional two days are subtracted.

If you are using Microsoft Excel version 4.0 and the NETWORKDAYS function is not available, you must install the Analysis ToolPak add-in macro. For more information on managing ad-ins, see "Managing Add-in Commands and Functions" in Chapter 4 of the "Microsoft Excel User's Guide 2."


KBCategory: kbusage
KBSubcategory:

Additional reference words: 7.00 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00 5.0 5.00 ATP Tool Pack ToolPack


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 20, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.