networking days exclude holiday but holidays data in another workbook

Question

Sub duration()
Dim lr As Long
Dim ws As ThisWorkbook
Set ws = ThisWorkbook

ls = Sheets("COBACOBA").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To ls
Cells(i, 29).Formula = Application.WorksheetFunction.NetworkDays_Intl(CDate(Cells(i, 16)), CDate(Cells(i, 28)))
Next i

End Sub

I have to find the number of working days between two dates which should exclude weekends and National Holidays. I am using function NETWORKDAYS in vba, this excludes weekends but I want to exclude Some National Holidays as well. How to use this function NETWORKDAYS(startDate, endDate, [holidays]) for National holidays. but my holidays list is on another excel file how to input data from my holiday.xlsx to my vba

Sub duration()

Dim ws As ThisWorkbook
Set ws = ThisWorkbook

Dim wk As Workbook



lr = Sheets("COBACOBA").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Cells(i, 29).Formula = Application.WorksheetFunction.NetworkDays_Intl(CDate(Cells(i, 16)), CDate(Cells(i, 28)), , Workbooks("Holiday.xlsx").Worksheets("Hol").Range("A2:A56"))

Next i

End Sub

Qualify your cell references with the sheet from the workbook you want then. — braX, Jun 25 '20 at 02:02
https://stackoverflow.com/questions/25871416/excel-fully-qualifying-range-cells-cells — braX, Jun 25 '20 at 02:32
https://stackoverflow.com/questions/58609653/run-time-error-1004-when-using-fully-qualified-cells-in-range-object — braX, Jun 25 '20 at 02:33
Dim wshol As Worksheet Set wshol = Workbooks("Holidays").Worksheets("Hol") Cells(i, 29).Formula = Application.WorksheetFunction.NetworkDays_Intl(CDate(Cells(i, 16)), CDate(Cells(i, 28)), CDate(wshol.Range(wshol.Cells(i, 1)))) — Lix Felix, Jun 25 '20 at 02:40
I don't know what you mean by "i want to exclude holidays to b" — jamheadart, Jun 25 '20 at 03:16

score 0 · Accepted Answer · answered Jun 25 '20 at 07:36

Sub duration()

Dim ws As ThisWorkbook Set ws = ThisWorkbook

Dim wk As Workbook

lr = Sheets("COBACOBA").Range("A" & Rows.Count).End(xlUp).Row For i = 2 To lr Cells(i, 29).Formula = Application.WorksheetFunction.NetworkDays_Intl(CDate(Cells(i, 16)), CDate(Cells(i, 28)), , Workbooks("Holidays.xlsx").Worksheets("Hol").Range("A2:A56")) Next i

End Sub

networking days exclude holiday but holidays data in another workbook

1 Answers1