Hello,

I have a need to calculate due dates that roll backwards should it fall on a weekend or holiday. I looked all over the net and found lots of code for similar calculations but none really did what I need and as I am not a pro coder I found that most of the code out there was beyond my ability to modify to my needs. And it most was long as well as complicated. I did however gain enough bits and pieces to put together the following code and since it works I thought I would share. Feel free to critique if you like but please remember that I am not a pro and as such I know that there is most likely something better than a loop function, but this works and it is simple to understand and use. (at any rate is better than the 5 to 10 queries I was using as a stop gap to do the same thing.)

The following code will require a holiday table to work.


Public Function DDt(ByRef startDate As Date, ByRef endDate As Date, Optional ByRef strHolidays As String = "Holidays") As Date


'
' Example:
' DDate(«startDate», «endDate», «strHolidays»)
' Due: DDate([StartDate],[StartDate}+10)
' If a holiday table/query is not specified it will default to a table or query that is named "Holidays"

Dim DueDt As Boolean
Dim Due As Date

Due = endDate

Do Until DueDt = True
DueDt = Weekday(Due) <> 1 And Weekday(Due) <> 7 And IsNull(DLookup("HoliDay", "Holidays", "[Holiday]=#" & Due & "#")) ' Holidays is the field containg the actual dates
If DueDt = False Then
Due = Due - 1 ' This code rolls the due back, to change it to roll forward replace -1 with +1
End If
Loop
DDt = Due
End Function