How do I carve out holidays from date calculations?

[Stephanie Moll]

Hello. I am using the below code courtesy of The Smiley Coder which works beautifully. I would now like to carve out holidays in addition to the weekends. I believe I will have to create a table of the holidays, but I'm not certain how to work it into the code. Any assistance is greatly appreciated.

Public Function addWorkDays(Dat eInput As Date, intDays As Integer) As Date
'Function that only adds days if they are workdays (monday-friday)
'Holidays are not excluded.

'Setup a working date
Dim myDT As Date
myDT = DateInput

'Setup number of days remaining to add
Dim intDaysRemain As Integer
intDaysRemain = intDays

'Now add days 1 at a time,
' while counting down intDaysRemain,
' but only if day is a working day
Do Until intDaysRemain = 0
myDT = DateAdd("d", 1, myDT)
Select Case Weekday(myDT)
Case vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday
intDaysRemain = intDaysRemain - 1
Case vbSaturday, vbSunday
'Don't modify intDaysRemain
End Select
Loop
'Return answer
addWorkDays = myDT

End Function

[gershwyn]

I think the simplest way to do that, given your existing code, is to create a table of holidays that your organization observes. We then just need to add a check to see if any given date is in that table.

I created a table called HolidayTable with two fields: HolidayName and HolidayDate, and filled it with a couple entries:

HolidayName   HolidayDate
New Year's    01/01/2011
Memorial Day  05/30/2011
4th of July   07/04/2011
 

Then add a couple lines to your code (added lines in bold):

Select Case Weekday(myDT)
  Case vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday
    'Check to see if holiday
    If DCount(1, "HolidayTable", "HolidayDate = #" & myDT & "#") < 1 Then
      intDaysRemain = intDaysRemain - 1
     End If
  Case vbSaturday, vbSunday
    'Don't modify intDaysRemain
End Select
 

This should now ignore any dates that are in your table.

?addWorkDays(#5/29/2011#, 7)
6/8/2011 
 
?addWorkDays(#5/30/2011#, 7)
6/8/2011 
 
?addWorkDays(#5/31/2011#, 7)
6/9/2011 
 

[Stephanie Moll]

Your code works perfectly! Thank you so much.