Dear Michael,
The rationale is ... laziness, pure programmer's laziness.
Because of the ambiguity caused by the word between I envisioned several possible values for the DateScope Enum. If I remember well old schoolday algebra, there are different types of intervals on the numeric scale: open, closed and half-open (left, right) depending on whether the boundaries were included or not.
After the facts, AlSayegh's reply to edderic seems to imply that he meant a closed interval.
My the original plan was to make allowance for the four possibilities. Very rapidly however the code became too strangled for the time I had available at that moment. So I dropped some constants out of the Enum, thereby reducing it to two values, one of which was choosen as default...
That being said, the simplified code above works correctly ... most of the time. In other words the code is flawed! A erroneous result is produced if Start date = End date = WhichDay (This is catched correctly by edderic's algorithm.
We also both assumed that End date had to be greater than Start date, which in normal spoken language is not necessarily the case. It is perfectly valid to ask: "How many saterdays are there between September 30 and september 1st?". So I decided to bite the bullet and the resulting comprehensive algorithm is produced below. Despite its length, the code is still faster than edderic's if the dates are sufficiently far apart.
Please not some changes in the naming of variables and default values. So far, my testing did not produce invalid results.
Private Const c_intDaysInWeek As Integer = 7
Private Enum DateInterval
NoneIncluded 'Neither Begin nor End included, Open Interval
BeginIncluded 'Begin included, End Not
EndIncluded 'End included, Begin not
BothIncluded 'Default Both Begin and End included, Closed Interval
End Enum
Private Function HowManyDaysOfWeek(datBegin As Date, datEnd As Date, WhichDay As Integer, _
Optional IntervalLimits As DateInterval = BothIncluded) As Integer
'* Purpose : Determines the number of DayOfWeek between two given dates
'* e.g. How many Saterdays are there between Jan 1st and August 15th?
'* Accepts : datBegin - First day of interval. e.g. Jan 1st, 2001
'* datEnd - Last day of interval. e.g. August 15th, 2001
'* WhichDay - Which day of the week, expressed as a VB System Constant
'* e.g. vbSaturday
'* IntervalLimits - an optional Enumerated variable of type DateInterval that
'* specifies if and which of the First or Last day are to be included in the
'* calculation. Possible values are: NoneIncluded, BeginIncluded, EndIncluded
'* and BothIncluded (Default value)
'* Returns : A positive or zero integer if calculation was successful
'* -1 if WhichDay is outside valid range of days
'* -2 if IntervalLimits is outside acceptable values
If WhichDay < vbSunday Or WhichDay > vbSaturday Then
HowManyDaysOfWeek = -1
GoTo PROC_EXIT
End If
If IntervalLimits < NoneIncluded Or IntervalLimits > BothIncluded Then
HowManyDaysOfWeek = -2
GoTo PROC_EXIT
End If
Select Case datEnd >= datBegin
Case True
Select Case Weekday(datBegin) = WhichDay
Case True
Select Case datEnd = datBegin
Case True
Select Case IntervalLimits
Case BothIncluded, BeginIncluded, EndIncluded
HowManyDaysOfWeek = 1
Case NoneIncluded
HowManyDaysOfWeek = 0
End Select
Case False 'datEnd <> datBegin
Select Case (datEnd - datBegin) Mod c_intDaysInWeek
Case Is = 0 'Interval = exact number of weeks
Select Case IntervalLimits
Case BothIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case BeginIncluded, EndIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
Case NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) - 1
End Select
Case Is > 0 'Interval <> exact number of weeks
Select Case IntervalLimits
Case BothIncluded, BeginIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case EndIncluded, NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
End Select
End Select
Case False 'datBegin <> WhichDay
Select Case Weekday(datEnd) = WhichDay
Case True
Select Case IntervalLimits
Case BothIncluded, EndIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case BeginIncluded, NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
Case False 'datEnd <> WhichDay
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
End Select
Case False 'datend < datBegin
Select Case IntervalLimits
Case NoneIncluded, BothIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, IntervalLimits)
Case BeginIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, EndIncluded)
Case EndIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, BeginIncluded)
End Select
End Select
PROC_EXIT:
Exit Function
End Function
Comments are wellcome! _________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]