[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Calculating a Due Date that is not a holiday or weekend

Teresa Smith

12/12/2006 4:21:00 AM

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
3 Answers

NickHK

12/12/2006 4:45:00 AM

0

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@aol.com> wrote in message
news:1165897276.144864.254800@j72g2000cwa.googlegroups.com...
> 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
>

Teresa Smith

12/13/2006 6:53:00 PM

0

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

12/14/2006 2:23:00 AM

0

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

<Smythe32@aol.com> wrote in message
news:1166035953.799534.59180@n67g2000cwd.googlegroups.com...
> 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,
>