Number of weekdays between two dates

Requirement:
To determine the number of workdays between two dates. I know I could use NETWORKDAYS (note: requires Analysis Toolpak) eg:

Date in A1:
1st January 2008
Date in B1:
10th January 2008

=NETWORKDAYS(A1,B1)

to return 8 days, but what happens if my weekend days are actually Monday and Tuesday?

Solution:
=SUM(INT((WEEKDAY(A1-{1,4,5,6,7})-A1+B1)/7))

which returns 7. The number values within the array constant ie {1,4,5,6,7} sets which days are working week days based on their value returned by the Weekday function ie:

1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

Hence to exclude Monday and Tuesday, the values 2 and 3 were omitted from the array constant {1,4,5,6,7}. To exclude Thursday and Friday you would use {1,2,3,4,7}. To exlcude Monday/Tuesday/Wednesday use {1,5,6,7} etc

Note this formula does not take into account holiday dates to exclude. If this is required use:

=SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(A1 & ":" & B1))),{1,4,5,6,7},0))*ISNA(MATCH(ROW(INDIRECT(A1 & ":" & B1)),holidays,0)))

Where "holidays" is a single column defined name range on your worksheet which holds the holiday dates you wish to exclude (eg in C1:C3 say). Again, you use the array constant {1,4,5,6,7} to define which days you consider to be working days.

Click to expand...

jonmo1 said:

I think the OP wants to know how many SUNDAYS there are between 5/1/2008 and 5/18/2008...

I'd like to know that one too...

Click to expand...

=SUM(INT((WEEKDAY(A1-{1})-A1+B1)/7))

=SUM(INT((WEEKDAY(A1-{1})-A1+B1)/7))

Click to expand...

Public Function HowManyDays(Sdate As Range, Edate As Range, Wday As Long)
Dim i
Dim MyCount As Long
For i = Sdate.Value To Edate.Value
    If Weekday(i) = Wday Then MyCount = MyCount + 1
Next i
HowManyDays = MyCount
End Function

Banker1 said:

If you want Sundays only it would be:

Code:

=SUM(INT((WEEKDAY(A1-{1})-A1+B1)/7))

Taken from above where {1} represents Sunday.

Click to expand...