Intervening Saturdays / Sundays

[Alex Motilal]

Hi Everybody,



At present I am using the following function to calculate the number of Intervening Week Ends falling between to dates:

Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As Integer

Dim AcDtStart As Date
Dim intSat As Integer
Dim intSun As Integer

AcDtStart = IIf(dtStart > dtEnd, dtEnd, dtStart)

'This function assumes dtStart <= dtEnd
CountWeekendDays2 = 0
intSat = DateDiff("d", GEDay(AcDtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(AcDtStart, 1), LEDay(dtEnd, 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


Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function

However, in some companies only the second Saturday of a month is off along with Sundays and in some other companies the second as well as the fourth Saturdays along with Sundays are off. In such cases what function has to be used to calculate the Intervening offs between two given dates?

With thanks in advance,
Alex

[Alex Motilal]

I also need the correct quotes if SpgFrNo is Integer in the following function:

Public Function GetStdSpgFrameHk(DtWorked As Date, SpgFrNo As String) As Single

GetStdSpgFrameHk = DLookup("[StdHkFixed]", "StdHkQry", "[StdHkFromDt]<= #" & DtWorked & "# And Nz([StdHkToDt],#12/31/9999#)>= #" & DtWorked & "# And [SpgFrameNumber]='" & [SpgFrNo] & "'")

End Function

Alex

[Alex Motilal]

I resolved my second question as follows:

Public Function GetStdSpgFrameHk(DtWorked As Date, SpgFrNo As Integer) As Single

GetStdSpgFrameHk = DLookup("[StdHkFixed]", "StdHkQry", "[StdHkFromDt]<= #" & DtWorked & "# And Nz([StdHkToDt],#12/31/9999#)>= #" & DtWorked & "# And [SpgFrameNumber]=" & [SpgFrNo] & )

End Function

Please give me the solution for the first part.

Alex