Greg said:
How do I calculate the number of days that have passed between two dates,
without counting weekends?
I count weekdays using the following module functions:
Public Function CountWeekdays(dtStart As Date, dtEnd As Date) As Integer
'Returns the number of weekdays regardless of whether they are a holiday
or not.
If dtStart <= dtEnd Then
CountWeekdays = DateDiff("d", dtStart, dtEnd) + 1 -
CountWeekendDays(dtStart, dtEnd)
Else
CountWeekdays = DateDiff("d", dtEnd, dtStart) + 1 -
CountWeekendDays(dtEnd, dtStart)
End If
End Function
Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As Integer
Dim intSat As Integer
Dim intSun As Integer
Dim dtBegin As Date
Dim dtFinish As Date
'Returns the number of weekend days regardless of whether they are a
holiday or not.
CountWeekendDays = 0
If dtStart <= dtEnd Then
dtBegin = dtStart
dtFinish = dtEnd
Else
dtBegin = dtEnd
dtFinish = dtStart
End If
intSat = DateDiff("d", GEDay(dtBegin, 7), LEDay(dtFinish, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtBegin, 1), LEDay(dtFinish, 1)) / 7 + 1
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function
Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function
Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function
James A. Fortune
(e-mail address removed)
