Faio said:
Can anyone help to provide a formulae to
.1. -count the days between two dates "startdate" and now() which exlude
weekends
2. -same as above but include weekends
Appreciate any help
Here's what I use to count weekend days:
'Begin Module Code-----
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
'Auxiliary Functions
Private Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function
Private Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function
Private Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'End Module Code-----
Do 2. first using the DateDiff function. Subtract the result of the
CountWeekendDays function from the total number of days to get the total
number of weekdays. Note that the CountWeekendDays function does not
loop through a date range, thus its computational speed will be more
consistent than that of looping methods. Also, there are some
situations where the DateDiff function gives incorrect results when
using "ww" so this code avoids that problem as well.
James A. Fortune
(e-mail address removed)
