Calculating a Due Date that is not a holiday or weekend

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Smythe32@aol.com - 12 Dec 2006 04:21 GMT

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

Reply to this Message

NickHK - 12 Dec 2006 04:45 GMT

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

> All,
>
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

Smythe32@aol.com - 13 Dec 2006 18:52 GMT

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,

Reply to this Message

NickHK - 14 Dec 2006 02:22 GMT

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

> 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
[quoted text clipped - 4 lines]
>
> Thanks,

Reply to this Message