How to Calculate a Projected Finish Date based on Work Week and Holidays

[mojado44]

All,

I have found the xNetWorkDays code on Google Groups from Myrna Larson
and it works great to calculate the number of work days between two
dates inluding a list of holidays and whether the work week is 5 , 6,
or 7 days. What I'm trying to do is find the projected finish date for
a given task based on the start date, the duration allocated for the
task, workweek type (Sunday off, Sat/Sun off, or no days off) and a
holiday list. I've been using other formulas but can't seem to get it
quite right. Any help would be greatly appreciated.

What I would be looking for is a function with the following arguments:

= EndDate(StartDate, DaysOff, Duration,Holidays) where:
StartDate = Start of the task
DaysOff is the work week type (5 for Sat/Sun off, 6 for only Sun off,
and 7 for no days off)
Duration = Number of days allocated for the task
Holidays = a range array of holidays that are not worked

Mojado44

 

[Ron Rosenfeld]

All,

I have found the xNetWorkDays code on Google Groups from Myrna Larson
and it works great to calculate the number of work days between two
dates inluding a list of holidays and whether the work week is 5 , 6,
or 7 days. What I'm trying to do is find the projected finish date for
a given task based on the start date, the duration allocated for the
task, workweek type (Sunday off, Sat/Sun off, or no days off) and a
holiday list. I've been using other formulas but can't seem to get it
quite right. Any help would be greatly appreciated.

What I would be looking for is a function with the following arguments:

= EndDate(StartDate, DaysOff, Duration,Holidays) where:
StartDate = Start of the task
DaysOff is the work week type (5 for Sat/Sun off, 6 for only Sun off,
and 7 for no days off)
Duration = Number of days allocated for the task
Holidays = a range array of holidays that are not worked

Mojado44

You should be able to adapt the WrkDay function below to your needs. Note that
the WrkDay function requires the NWrkDays function also, although you don't
need to use it in your worksheet unless you want to.

To get an end date:

Start Date: A1
Days Off Sun only
Duration: A2
Holidays: E1:E10

You would enter:

=wrkday(A1,A2,E1:E10,1)

For no days off:

=wrkday(A1,A2,E1:E10)

For Sat & Sun off

=wrkday(A1,A2,E1:E10,1,7)

(or you could use the ATP function)


============================
Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0, _
Optional WeekendDay_4 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7

'credits to Myrna

Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean

DoHolidays = Not (Holidays Is Nothing)

SD = StartDate: ED = EndDate
If SD > ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If

w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3, WeekendDay_4
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function

Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0, _
Optional WeekendDay_4 As Integer = 0) As Date

' Sunday = 1; Monday = 2; ... Saturday = 7

Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim Temp As Long, SD As Date, ED As Date

Stp = Sgn(NumDays)

'Add NumDays
TempDate = StartDate + NumDays

'Add Non-Workdays

Do While Abs(NumDays) <> Temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)

Temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3,
WeekendDay_4)
TempDate = TempDate + NumDays - Stp * (Temp)
Loop

WrkDay = TempDate
End Function
=====================================
--ron

 

[mojado44]

This works great! Thanx!

mojado44

 

[Ron Rosenfeld]

This works great! Thanx!

mojado44

You're very welcome.


--ron