Calculating a Due Date that is not a holiday or weekend

[Smythe32]

All,

Looking for some assistance here. I need to take a date, add a number
of CALENDAR days and then ensure that the new date is not a holiday or
weekend. I cannot use workdays because it adds just workdays and I
need to add calendar days.

Ex: Start Date 12/22/06 add 10 calendar days. Holidays 12/25/06,
1/1/07. Ending date should be:
01/02/07.

Any help would be greatly appreciated.

Thanks

 

[NickHK]

Using VBA, you can use the DateAdd to add the required time period and
Weekday to check if that is vbSaturday or vbSunday (assuming that is your
weekend).
As for holidays, have an array of your holiday dates and loop through seeing
if you are on a holiday.
If your end date falls on a non-working day, +1 day and test again.

NickHK

 

[Smythe32]

Ok Thank you. I just can't figure you how the code is written. I can
write some code but I am having issues with this one. I am going to
put it right out there.

Does anyone have any code already written that performs the function I
am looking for?

Thanks,

 

[NickHK]

This should get you started;

Public Function GetDate_WorkingDay(StartDate As Date, DaysToAdd As Long) As
Date
Dim TestDate As Long 'Date

Dim Hols(1 To 5) As Long
'Just creat some holidays
Hols(1) = DateSerial(2006, 12, 25)
Hols(2) = DateSerial(2006, 12, 26)
Hols(3) = DateSerial(2006, 12, 27)
Hols(4) = DateSerial(2007, 1, 1)
Hols(5) = DateSerial(2007, 1, 2)

TestDate = DateAdd("d", DaysToAdd, StartDate)

Do Until IsWorkingDay(TestDate, Hols()) = True
TestDate = TestDate + 1
Loop

GetDate_WorkingDay = TestDate

End Function


Private Function IsWorkingDay(DateToCheck As Long, HolidayDates() As Long)
As Boolean
Dim i As Long

'Assume failure
IsWorkingDay = False

If Weekday(DateToCheck, vbMonday) > 5 Then
Exit Function
End If

For i = LBound(HolidayDates) To UBound(HolidayDates)
If DateToCheck = HolidayDates(i) Then
Exit Function
End If
Next

'Get here, then OK
IsWorkingDay = True

End Function

NickHK