Adapt DateAdd Module

[Neilbees]

Afternoon all

I hope someone can help with this problem. I use the following code (taken from here obviously - thanks Rural Guy) to DateAdd taking into account working days and holidays - all works perfectly.

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
intNumDays = intNumDays - 1
End If
Loop
End Function

However, I need to adapt the code to subtract days from a given date. When I enter a minus figure this code returns the same day as the given date. Can someone help me out with this? Access 2002 if that matters.

 

[RuralGuy]

I actually use a different function for that.

Public Function MinusWorkdays(dteStart As Date, intNumDays As Long) As Date

MinusWorkdays = dteStart
Do While intNumDays > 0
     MinusWorkdays = DateAdd("d", -1, MinusWorkdays)
     If Weekday(MinusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
'     If Weekday(MinusWorkdays, vbMonday) <= 5 And _
      IsNull(DLookup("[Holiday]", "tblHolidays", _
      "[HolDate] = " & Format(MinusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")))  Then
          intNumDays = intNumDays - 1
     End If
Loop
End Function

Or if you want a function that can do both then:

Public Function AdjWorkDays(dteStart As Date, _
                            intNumDays As Long, _
                            Optional blnAdd As Boolean = True) As Date
AdjWorkDays = dteStart
Do While intNumDays > 0
   If blnAdd Then
      '-- Adding WorkDays
      AdjWorkDays = AdjWorkDays + 1
   Else
      '-- Subtracting WorkDays
      AdjWorkDays = AdjWorkDays - 1
   End If
   If Weekday(AdjWorkDays, vbMonday) <= 5 Then
'-- Use the following code instead, if you have a "Holiday" table
'   If Weekday(AdjWorkDays, vbMonday) <= 5 _
      And IsNull(DLookup("[Holiday]", "tblHolidays", _
      "[HolDate] = " & Format(AdjWorkDays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")))  Then
      intNumDays = intNumDays - 1
   End If
Loop
End Function

 

[Neilbees]

That's absolutely fantastic RG, you're a genius. Thanks for posting back so quickly too.

 

[RuralGuy]

You're welcome and have fun.