I found these 2 examples online on how to count Mondays between 2 dates but neither seems to work on Excel 2003, which I am using. Can anyone help me please?
EXAMPLE 1
Number Of Mondays In Period
If you need to return the number of Mondays (or any other day) that occur within an interval between two dates, use the following Array Formula:
=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
EXAMPLE 2
Copy the code below to a tab sheet module, the name must be unchanged.
Then on your worksheet have a cell for the "Start date," another for the "End date" and a cell for the days of the week to be counted "WkDays" as a data cell.
In the cell you want your day count to be listed put:
=WkDays(StartDate, EndDate, WkDays)
Like,
=WkDays(B3,B4,B5)
The key to WkDays is: 1=Monday...7=Sunday.
To count Saturdays and Sundays, WkDays would be 67, to count Saterday WkDays would be 6, to count Monday through Friday WkDays would be 12345 as day data.
Function WkDays(StartDate As Date, EndDate As Date, _
Days As Long) As Integer
' Returns the number of qualifying days between (and including)
' StartDate and EndDate. Qualifying days are whole numbers where
' each digit represents a day of the week that should be counted,
' with Monday=1, Tuesday=2, etc. For example, all Mondays, Tuesdays
' and Thursdays are to be counted between the two dates, set
' WkDays = 124 on your worksheet.
'
Dim iDate As Date
Dim strQdays As String
strQdays = CStr(Days)
WkDays = 0
For iDate = StartDate To EndDate
If strQdays Like "*" & CStr(Weekday(iDate, vbMonday)) & "*" Then
WkDays = WkDays + 1
End If
Next iDate
End FunctionThank you.
Erik
