DateAdd exclude Weekends

[Shaft11]

I know this is a 'dead horse' here however, I've searched this site and still can't get it right...

I have a field named [court_date] and a field named [serve_by]. The [served_by] date has to be 7 working days before the [court date].

I copied and pasted the following function from this site:

'********************************************************** 
'Declarations section of the module 
'********************************************************** 
 
Option Explicit 
 
'========================================================== 
' The DateAddW() function provides a workday substitute 
' for DateAdd("w", number, date). This function performs 
' error checking and ignores fractional Interval values. 
'========================================================== 
Function DateAddW (ByVal TheDate, ByVal Interval) 
 
   Dim Weeks As Long, OddDays As Long, Temp As String 
 
   If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _ 
              VarType(Interval)  > 5 Then 
      DateAddW = TheDate 
   ElseIf Interval = 0 Then 
      DateAddW = TheDate 
   ElseIf Interval > 0 Then 
      Interval = Int(Interval) 
 
   ' Make sure TheDate is a workday (round down). 
 
      Temp = Format(TheDate, "ddd") 
      If Temp = "Sun" Then 
         TheDate = TheDate - 2 
      ElseIf Temp = "Sat" Then 
         TheDate = TheDate - 1 
      End If 
 
   ' Calculate Weeks and OddDays. 
 
      Weeks = Int(Interval / 5) 
      OddDays = Interval - (Weeks * 5) 
      TheDate = TheDate + (Weeks * 7) 
 
  ' Take OddDays weekend into account. 
 
      If (DatePart("w", TheDate) + OddDays) > 6 Then 
         TheDate = TheDate + OddDays + 2 
      Else 
         TheDate = TheDate + OddDays 
      End If 
 
      DateAddW = TheDate 
Else                         ' Interval is < 0 
      Interval = Int(-Interval) ' Make positive & subtract later. 
 
   ' Make sure TheDate is a workday (round up). 
 
      Temp = Format(TheDate, "ddd") 
      If Temp = "Sun" Then 
         TheDate = TheDate + 1 
      ElseIf Temp = "Sat" Then 
         TheDate = TheDate + 2 
      End If 
 
   ' Calculate Weeks and OddDays. 
 
      Weeks = Int(Interval / 5) 
      OddDays = Interval - (Weeks * 5) 
      TheDate = TheDate - (Weeks * 7) 
 
   ' Take OddDays weekend into account. 
 
      If (DatePart("w", TheDate) - OddDays) > 2 Then 
         TheDate = TheDate - OddDays - 2 
      Else 
         TheDate = TheDate - OddDays 
      End If 
 
      DateAddW = TheDate 
    End If 
 
End Function

However, I can't get it to work.

When I put in the code:

Serve_by= DateAddW ("d", -7, [court_date])

I get "Complie Error: Wrong Number of Arguments or Invalid Property Assignments"

When I put in the code:

Serve_by= DateAddW(-7, [Court_date])

It returns "01/01/2010"

What am I doing Wrong?

[nico5038]

You should use:
DateAddW( [Court_date], -7)
as the function has two arguments:
TheDate AND Interval

Nic;o)

[Shaft11]

Thank you for your reply,

However, It seems that the function is counting 7 weeks, not 7 days. Is there something that I need to change in the function or the code?

[NeoPa]

It's a function to move a number of weeks.

Check out Function to Move (Forwards or Backwards) Through Weekdays instead.

[Shaft11]

How do I call the function?

[NeoPa]

RequiredDate = MoveWD(datThis:=OriginalDate, intInc:=NoOfWDays)