XL: Formula to Find Number of Weekdays Between Dates
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, versions 7.0, 7.0a
-
Microsoft Excel 97 for Windows
-
Microsoft Excel 98 Macintosh Edition
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 or later 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."
Additional query words:
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 ATP Tool Pack ToolPack 97 98 XL98 XL97 XL7 XL5 XL4 XL3
Keywords :
Version : WINDOWS:2.x,3.x,4.x,5.0,5.0c,7.0,7.0a,97; MACINTOSH:98
Platform :
Issue type :
|