I have the following code in a module:

Private Sub END_DATE_AfterUpdate()
Dim dif As Integer
Dim i As Integer
Dim dCount As Integer
Dim midTemp

   dCount = 1

   dif = DateDiff("d", START_DATE, END_DATE, vbMonday, vbFirstJan1)
   For i = 1 To dif
       midTemp = DateAdd("d", i, START_DATE)
       If Weekday(midTemp, vbMonday) < 6 Then
            dCount = dCount + 1
        End If
    Next i
NUM_DAYS = dCount
End Sub

I used the code from an existing post(changed accordingly) and it works great. It pulls the Start and End date from my form. Now I would like to be able to have the code subtract out holidays that I determine. Any easy ideas?

Thanks.

Dan

Dan,

Clever code...

FYI: the DateAdd and DateDiff functions already have a "w" option (weekday) that counts weekdays already, so no need to use "d" (day) and then pull out weekends manually.

As for holidays, since they vary from worksite to worksite, the easiest thing I can think of is for you to create a holidays table with fields strName, intMonth, and intDay. Omit the year because you want your table to work for reoccuring holidays.

In your code, loop through the table, construct a current holiday

dtmHoliday = DateSerial(Year(Date()), intMonth, intDay)

if it falls between the start and end dates, then subtract it from your total.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division