Count Saturdays
Posted by Marvin on November 20, 2001 10:50 AM
Is there a way to set up a formula or function to count the number of Saturdays or Sundays between two given dates?
Posted by IML on November 20, 2001 11:07 AM
Not fully tested, but try
=INT(((A2-((WEEKDAY(A2)<>7)*WEEKDAY(A2)))-A1)/7)+1
to count saturdays.
A1 is start date, a2 is end date. This will include A1 or B2 in the count if it is a saturday.
Posted by Joe Was on November 20, 2001 2:45 PM
Most of this code was posted on this site.
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 Function
'JSW
