I am newbie, can anyone guide me on writing simple script based on my below requirement.?
1) Array for inputting own public holidays.
2) Add 2 days excluding weekend and public holidays in Array to current Date.
3) Array output is captured based on user input of public holiday list field
Thanks a lot.
Sub Add_Hours_Version1()
Const HOURS_PER_DAY As Long = 8
Dim StartDate As Date
Dim NumDays As Long
Dim HoursToAdd As Long
Dim HolidaysList() As Variant
Dim holiday As Variant
Dim bWasFound As Boolean
' edit/add/delete as needed
' these are US Federal Holidays
' taken from http://www.opm.gov/Operating_Status_Schedules/fedhol/2009.asp
HolidaysList = Array("1/1/2009", "1/19/2009", "2/16/2009", _
"5/25/2009", "7/3/2009", "9/7/2009", _
"10/12/2009", "11/11/2009", "11/26/2009", "12/25/2009")
StartDate = InputBox("Enter start date")
HoursToAdd = InputBox ("Enter number of hours")
' round up number of days
NumDays = WorksheetFunction.Ceiling(HoursToAdd / HOURS_PER_DAY, 1)
' increase date
StartDate = StartDate + NumDays
' check if it's a holiday
For Each holiday In HolidaysList
If holiday = StartDate Then
bWasFound = True
Exit For
End If
Next holiday
If bWasFound Then
StartDate = StartDate + 1
End If' check if it's a weekend, if so then move it to Monday
If Weekday(StartDate, vbMonday) = 6 Then
StartDate = StartDate + 2
ElseIf Weekday(StartDate, vbMonday) = 7 Then
StartDate = StartDate + 1
End If
End Sub