Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 821091 - Last Review: August 25, 2008 - Revision: 1.1
HowTo: Formula to Find Number of Weekdays Between Dates
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:
NOTE: The dates in A1 and A2 must be weekdays, or the result may not be correct.
NOTE: the NETWORKDAYS() function is included in the Microsoft Excel Analysis Toolpack Add-In.
This function is designed to exclude Saturday and Sunday as the weekend.
In all versions of Microsoft Excel including version 4.0 the following formula can be used to find the number of weekdays between two dates, where Thursday and Friday are the weekend:
=A2-A1+1-INT((A2-A1)/7)*2-IF(AND(WEEKDAY(A2)<>7,WEEKDAY(A1)<>7,WEEKDAY(A2)<WEEKDAY(A1),WEEKDAY(A1)<>6),2,IF(AND(WEEKDAY(A2)=7,WEEKDAY(A1)<=5),2,IF(AND(WEEKDAY(A2)=6,WEEKDAY(A1)<=5),2,IF(OR(WEEKDAY(A1)=6,WEEKDAY(A2)=5),1,IF(AND(WEEKDAY(A1)=7,WEEKDAY(A2)=6),2,0)))))-IF(AND(WEEKDAY(A1)=6,WEEKDAY(A2)=5),1,0)
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 Thursday and a Friday 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.
APPLIES TO
- Microsoft Excel 5.0 Standard Edition
- Microsoft Excel 95 Standard Edition
- Microsoft Excel 97 Service Pack 2
- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 2002 Standard Edition
| kblangsupparabic kbhowto kbbidi KB821091 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate