Help with module requested!!!

  • Thread starter Help with a Module Requested
  • Start date
H

Help with a Module Requested

We have a database that records the ship date and receipt date and we are
trying to disregard weekends and holidays. The problem is that it only
disregards the first weekend and then the elseif is met and it quits. If a
transit goes over two weekends (for example, takes 14 calendar days in
transit), then only the first weekend is credited. How should the elseif
read? Here's what we have for January, for example:
Function IT(SDAY As Date, RDAY As Date)
If #12/24/2004# >= SDAY And #12/27/2004# <= RDAY Then
IT = ((RDAY - SDAY) - 4)
ElseIf #12/31/2004# >= SDAY And #1/3/2005# <= RDAY Then
IT = ((RDAY - SDAY) - 4)
ElseIf #1/8/2005# >= SDAY And #1/9/2005# <= RDAY Then
IT = ((RDAY - SDAY) - 2)
ElseIf #1/15/2005# >= SDAY And #1/16/2005# <= RDAY Then
IT = ((RDAY - SDAY) - 2)
ElseIf #1/22/2005# >= SDAY And #1/23/2005# <= RDAY Then
IT = ((RDAY - SDAY) - 2)
ElseIf #1/29/2005# >= SDAY And #1/30/2005# <= RDAY Then
IT = ((RDAY - SDAY) - 2)
Else: IT = RDAY - SDAY
End If
End Function
 
N

Naresh Nichani MVP

Hi:

Try this function --

Function CountDates(dt1 As Date, dt2 As Date) As Long
Dim l As Long
Dim dt3 As Date

dt3 = dt1
Do While dt3 <= dt2
If VBA.DateTime.Weekday(dt3) = 1 Or VBA.DateTime.Weekday(dt3) = 7 Then

Else
l = l + 1
End If
dt3 = DateAdd("d", 1, dt3)
Loop
CountDates = l
End Function

Here dt1 is start date and dt2 is End Date - it will ignore all Sat and Sun
and return the rest.

Regards,

Naresh Nichani
Microsoft Access MVP

"Help with a Module Requested" <Help with a Module
[email protected]> wrote in message
news:[email protected]...
 
Top