Mr. Muzak said:
Let me set it up for you.
I have several people with different days off. I'm trying to count the
total number of days they are out ill without counting their Days off. I
have a query that takes the 1st date they were out and subtracts it from the
current date giving me the total # of days out. In the query I have the
persons days off noted in different columns under the header of 1st DayOff &
2nd DayOff.
So how do I tell the query to count the # of days from 1/1/07 - 2/28/07
excluding every Thursday and Friday??
Try this:
'---------Begin code---------
Public Function CountExcludingTwoDays(dtStart As Date, dtEnd As Date,
vbXDay1 As Integer, vbXDay2 As Integer) As Integer
Dim intDay1 As Integer
Dim intDay2 As Integer
Dim dtBegin As Date
Dim dtFinish As Date
'Returns the number of days excluding vbXDay1 and vbXDay2 regardless of
whether
'they are a holiday or not. The two dates are forced to be treated in
order.
CountExcludingTwoDays = 0
If dtStart <= dtEnd Then
dtBegin = dtStart
dtFinish = dtEnd
Else
dtBegin = dtEnd
dtFinish = dtStart
End If
intDay1 = DateDiff("d", GEDay(dtBegin, vbXDay1), LEDay(dtFinish,
vbXDay1)) / 7 + 1
intDay2 = DateDiff("d", GEDay(dtBegin, vbXDay2), LEDay(dtFinish,
vbXDay2)) / 7 + 1
CountExcludingTwoDays = DateDiff("d", dtStart, dtEnd) + 1 -
Ramp(intDay1) - Ramp(intDay2)
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
Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'----------End code----------
Using your example:
CountExcludingDays(#1/1/2007#, #2/28/2007#, 5, 6) => 43
Be sure to test this code thoroughly. Note: I didn't enforce that
vbXDay1 <> vbXDay2 so don't try it with the same day twice! The code
sacrifices for efficiency and for the desirable property that the
execution time is almost independent of the time span, some of the
intuition inherent in solutions where days iterate. The Ramp function
is used to eliminate extraneous negative values such as those caused by
a time interval being only a few days in length. The LEDay and GEDay
functions return the date of a given weekday on or immediately prior to
the input date, or on or immediately after the input date.
James A. Fortune
(e-mail address removed)
