Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a Due Date that is not a holiday or weekend
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a Due Date that is not a holiday or weekend
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 wrote in message oups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a Due Date that is not a holiday or weekend
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a Due Date that is not a holiday or weekend
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 wrote in message ups.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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count days but solutions can't land on holiday or weekend | Excel Discussion (Misc queries) | |||
formual to determine if date falls on weekend, adjust date to Mond | Excel Discussion (Misc queries) | |||
Removing weekend/holiday dates - cont'd | Charts and Charting in Excel | |||
Invalid date if weekend or bank holiday | Excel Worksheet Functions | |||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend? | Excel Programming |