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.

Open your workbook
Press ALT-F11 to open VBA Editor
Insert Module
Paste the code
Come back to your worksheet
Enter Start date in C1, End date in D1, Define range Holidays which contain
holidays
(Select the cells containing the holiday dates, type Holidays where you
normally see the cell address and press ENTER)

Enter
=NWD(C1, D1, Holidays)
It should work


"Katie" wrote:

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.

Sheeloo,

Thank you, when I typed it in a blank worksheet I did get it to work however
when I put it in my schedule it errors out. This time I receive a #value
error. Any ideas?

"Sheeloo" wrote:

Open your workbook
Press ALT-F11 to open VBA Editor
Insert Module
Paste the code
Come back to your worksheet
Enter Start date in C1, End date in D1, Define range Holidays which contain
holidays
(Select the cells containing the holiday dates, type Holidays where you
normally see the cell address and press ENTER)

Enter
=NWD(C1, D1, Holidays)
It should work


"Katie" wrote:

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.

Make sure that all the cells referred to in the formula contain valid dates...

Have you correctly defined the holidays range?

if it does not work then you can send the file to me.
"Katie" wrote:

Sheeloo,

Thank you, when I typed it in a blank worksheet I did get it to work however
when I put it in my schedule it errors out. This time I receive a #value
error. Any ideas?

"Sheeloo" wrote:

Open your workbook
Press ALT-F11 to open VBA Editor
Insert Module
Paste the code
Come back to your worksheet
Enter Start date in C1, End date in D1, Define range Holidays which contain
holidays
(Select the cells containing the holiday dates, type Holidays where you
normally see the cell address and press ENTER)

Enter
=NWD(C1, D1, Holidays)
It should work


"Katie" wrote:

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.

To count Monday thru Saturday and exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D14&":"&E14)),2)<7),--(ISNA(MATCH(ROW(INDIRECT(D14&":"&E14)),C2:C10,0))) )

Where C2:C10 is the list of holiday dates

--
Biff
Microsoft Excel MVP


"Katie" wrote in message
...
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.