K
Katie
I have a schedule that I am trying to calculate the difference between the
start and the end date (minus the holidays), however I need it to include
Saturdays as a workday. I saw a previous post where they gave some code to
enter however when I enter it into the cell it does not recognize the created
function. Could you please help me out.
The cell contains the information : =nwd(D14,E14,Holiday)
The code is as follows:
Function NWD(StartDate As Date, EndDate As Date, _
Holidays As Range, _
Optional WeekendDay_1 As Integer = 1, _
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 = True
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
NWD = Count
End Function
Could someone please let me know what I am doing wrong.
start and the end date (minus the holidays), however I need it to include
Saturdays as a workday. I saw a previous post where they gave some code to
enter however when I enter it into the cell it does not recognize the created
function. Could you please help me out.
The cell contains the information : =nwd(D14,E14,Holiday)
The code is as follows:
Function NWD(StartDate As Date, EndDate As Date, _
Holidays As Range, _
Optional WeekendDay_1 As Integer = 1, _
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 = True
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
NWD = Count
End Function
Could someone please let me know what I am doing wrong.