How do I use the WORKDAY() for a 6days working wk excluding holida

  • Thread starter Thread starter JP.F
  • Start date Start date
J

JP.F

Could someone help me with below code from "Rick Rothstein (MVP - VB)" where
holidays are excluded .
Many thanks,
JP.F
 
This seems to work

Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long, _
Holidays As Variant) As Date
Dim TheseHols As Variant
Dim DateAdded As Date
Dim mDays As Long
Dim HolsIndex As Long
Dim i As Long

If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAdded = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) > _
7 - Weekday(StartDate)), StartDate)
If Not IsEmpty(Holidays) Then

ReDim TheseHols(LBound(Holidays) To UBound(Holidays))
HolsIndex = LBound(TheseHols)
For i = LBound(Holidays) To UBound(Holidays)
If Holidays(i) >= StartDate And Holidays(i) <= DateAdded And _
Weekday(Holidays(i)) <> 1 Then
mDays = mDays + 1
Else
TheseHols(HolsIndex) = Holidays(i)
HolsIndex = HolsIndex + 1
End If
Next i
End If
If mDays <> 0 Then

If HolsIndex = LBound(Holidays) Then
TheseHols = Empty
Else
ReDim Preserve TheseHols(LBound(TheseHols) To HolsIndex - 1)
End If
DateAdded = DateAddSixDayWorkweek(DateAdded, mDays, TheseHols)
End If
DateAddSixDayWorkweek = DateAdded
End Function

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
JP.F,

Here's a version that works with the holiday dates entered into a range of
cells, used like

=DateAddSixDayWorkweek(C2,C3,A2:A4)

where A2:A4 has holiday dates- which should be entered in ascending order.

HTH,
Bernie
MS Excel MVP



Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long, Holidays As Range) As Date
Dim myC As Range

If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) > _
7 - Weekday(StartDate)), StartDate)

For Each myC In Holidays
If myC.Value >= StartDate And myC.Value <= DateAddSixDayWorkweek Then
DateAddSixDayWorkweek = DateAddSixDayWorkweek + _
IIf(Weekday(DateAddSixDayWorkweek + 1) = 1, 2, 1)
End If
Next myC

End Function
 
Bob,

This code blows up here:

ReDim TheseHols(LBound(Holidays) To UBound(Holidays))

if Holidays are entered as a range of cells.

Bernie
 
Back
Top