Calculate working days from start date

In the comments for Calculate Working Days Minus Holidays in VBA, Dave asks if there is a function that lets you specify a start date and number of hours, and have the function return the ending business day.

There are two ways to do this. The first way is simple math.

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

The other way is to use the WORKDAY function in VBA. First you'll need to set a reference to the Analysis ToolPak (ATP). Check out Calculate Working Days Minus Holidays in VBA for instructions on how to do that.

Sub Add_Hours_Version2()
Dim HolidaysList() As Variant
Const HOURS_PER_DAY As Long = 8
Dim StartDate As Date
Dim NumDays As Long
Dim HoursToAdd As Long

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)

MsgBox workday("1/1/2008", NumDays, HolidaysList)
End Sub

And once again I like my version better.

Related Articles:

• January 30, 2012 -- Remove Flags from Inbox Emails
• January 9, 2012 -- Annoying Website Name Generator in VBA and PHP
• December 26, 2011 -- Validating Userform Data