Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

HowTo: Formula to Find Number of Weekdays Between Dates


View products that this article applies to.

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.

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)

↑ Back to the top


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 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.

↑ Back to the top


Keywords: KB821091, kbbidi, kbhowto, kblangsupparabic

↑ Back to the top

Article Info
Article ID : 821091
Revision : 2
Created on : 8/25/2008
Published on : 8/25/2008
Exists online : False
Views : 417