Monday, February 18, 2008

Number of sundays in a period function

Public Function BizDateDiff(ByVal varDateStart As Date, ByVal varDateEnd As Date, DayNumber as Integer ) As Integer ‘ DayNumber (sunday =1,monday=2…)
Dim varNextDate As Date
'‘This function calculates the weekdays between two dates.
‘Exit if variables not a valid date
If Not IsDate(varDateStart) Or Not IsDate(varDateEnd) Then
BizDateDiff = 0
Exit Function
End If
varNextDate = varDateStart
BizDateDiff = 0
While Not varDateEnd< varNextDate
If Weekday(DateValue(varNextDate)) = DayNumber Then
BizDateDiff = BizDateDiff + 1 ' Number of Day number Days (eg: sunday)
varNextDate = DateAdd("d", 7, varNextDate)
Else
varNextDate = DateAdd("d", 1, varNextDate)
End If
Wend
BizDateDiff = Day(varDateEnd) - BizDateDiff ' Number of working days or excluded the Day number day
End Function

No comments: