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



Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function

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" wrote in message
...
Could someone help me with below code from "Rick Rothstein (MVP - VB)"
where
holidays are excluded .
Many thanks,
JP.F



Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function

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

"JP.F" wrote in message
...
Could someone help me with below code from "Rick Rothstein (MVP - VB)"
where
holidays are excluded .
Many thanks,
JP.F



Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function

Bob,

This code blows up he

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

if Holidays are entered as a range of cells.

Bernie


"Bob Phillips" wrote in message
...
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" wrote in message
...
Could someone help me with below code from "Rick Rothstein (MVP - VB)"
where
holidays are excluded .
Many thanks,
JP.F



Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function

Bernie,

Many Thanks for your help,
Have a nice day,

JP.F

"Bernie Deitrick" wrote:

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

"JP.F" wrote in message
...
Could someone help me with below code from "Rick Rothstein (MVP - VB)"
where
holidays are excluded .
Many thanks,
JP.F



Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function

Bob,

Thanks for your help, I used codes received from Bernie.
Have a nice day,
JP.F


"Bob Phillips" wrote:

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" wrote in message
...
Could someone help me with below code from "Rick Rothstein (MVP - VB)"
where
holidays are excluded .
Many thanks,
JP.F



Function DateAddSixDayWorkweek(ByVal StartDate As Date, _
WorkDays As Long) As Date
If Weekday(StartDate) = 1 Then StartDate = StartDate - 1
DateAddSixDayWorkweek = DateAdd("d", 7 * (WorkDays \ 6) + _
(WorkDays Mod 6) - ((WorkDays Mod 6) _
7 - Weekday(StartDate)), StartDate)
End Function