Function to calculate number of Wednesdays in time interval

[birdman300]

Hi all.

I am trying to write a function in VBA to calculate the number of Wednesdays in a century. I'm new to VBA, and not really sure how to start it, Also, do the built in date functions take into consideration of leap years? The interval is 1 Jan 2001 to 31 Dec 3000..

 

[PaulCookGiles]

Dunno if you've already figured this out, but if not.. (There are 52,230 Wednesdays in the fourth millennium. )

Public Function CountWednesdays(dtStartDt As Date, dtEndDt As Date) As Long
Dim dtCurrent As Date, dtFirstWed As Date

dtCurrent = dtStartDt

'find first Wednesday in range:
Do Until Weekday(dtCurrent) = 4
If Weekday(dtCurrent) = 4 Then
dtFirstWed = dtCurrent
Else
dtCurrent = DateAdd("d", 1, dtCurrent)
End If
Loop

'add 1 to the count, and increment current date by a week:
CountWednesdays = 1
dtCurrent = DateAdd("d", 7, dtCurrent)

'add 7 days to first Wednesday until out of range:
Do Until dtCurrent > dtEndDt
CountWednesdays = CountWednesdays + 1
dtCurrent = DateAdd("d", 7, dtCurrent)
Loop

End Function