neveu:
Code can be more efficient in many cases than formulas although here the only advantage is to make entry of the formula easier. A UDF could be an advantage because you have a custom version of the NETWORKDAYS function that requires entering or changing the cell addresses in several places.
The UDF solution below is incomplete and has the disadvantage that the user has to establish a reference to the NETWORKDAYS function in the code. The solution is incomplete because the TRUNC function appears not to be working as desired in the code. I did not take the time to figure out what the TRUNC function is doing here.
<<<<<<<<<<<<<<<<<<<<<<<<
HERE IS AN ALTERNATIVE USING NAMED RANGES
If the date cells are always in the same relative position, then a named range could be used for each of the cells A3 and B3.
legal_holydays =Sheet1!$E$2:$E$8
These Named Ranges are shown as entered when cursor is iin Cell C10:
Holi1 =Sheet1!A10
Holi2 =Sheet1!B10
Cell formula in Cell C10:
=IF(NETWORKDAYS(Holi1+1,Holi2-1,legal_holydays)<0,0,NETWORKDAYS(Holi1+1,Holi2-1,legal_holydays))+IF(WEEKDAY(Holi1,2)>5,0,1-(Holi1-TRUNC(Holi1)))+IF(WEEKDAY(Holi2,2)>5,0,Holi2-TRUNC(Holi2))
<<<<<<<<<<<<<<<<<<<<<<<<
Partial UDF solution:
I was able to convert part of this formula to code.
Note that this partial formula does not give the same result as your full formula.
In Module1:
Rich (BB code):
Option Explicit
Function WorkDae(vDx As Date, vDy As Date, legal_holydays As Range)
WorkDae = IIf(NETWORKDAYS(vDx + 1, vDy - 1, legal_holydays) < 0, 0, NETWORKDAYS(vDx + 1, vDy - 1, legal_holydays))
End Function
For the
NETWORKDAYS FUNCTION to work in VBA code, since it is an addin FUNCTION:
ALT F11
Tools
References
and check box next to "atpvbavn.xls"
As for the version below, I am not sure why it does not work, or why the TRUNC function is being used, but this should give you a start on converting the cell formula to a UDF. It appears that crimson_b1ade may be right, and your current formula is more efficient, unless you can finish converting the formula to code.
Rich (BB code):
Option Explicit
Function WorkDae(vDx As Date, vDy As Date, legal_holydays As Range)
WorkDae = IIf(NETWORKDAYS(vDx + 1, vDy - 1, legal_holydays) < 0, 0, NETWORKDAYS(vDx + 1, vDy - 1, legal_holydays)) + IIf(Weekday(vDx, 2) > 5, 0, 1 - (vDx - Application.WorksheetFunction.TRUNC(vDx))) + IIf(Weekday(vDy, 2) > 5, 0, vDy - Application.WorksheetFunction.TRUNC(vDy))
End Function
Here are the formulas:
Rich (BB code):
A B C D E
1 Date1 Date2 Dif Holidays
2 1-Jan 5-May 86 1-Jan
3 4-Jan 25-May 99 17-Feb
4 7-Jan 14-Jun 110 4-Jul
5 10-Jan 4-Jul 124 30-Sep
6 13-Jan 24-Jul 136 15-Nov
7 25-Dec
8 25-Jan 12-Oct 184 20-Feb
Sheet1
[Table-It] version 09 by Erik Van Geit
Rich (BB code):
RANGE FORMULA (1st cell)
C2:C6 =WorkDae(A2,B2,legal_holydays)
C8 =IF(NETWORKDAYS(Holi1+1,Holi2-1,legal_holydays)< 0,0,NETWORKDAYS(Holi1+1,Holi2-1,legal_holydays))+IF(WEEKDAY(Holi1,2)>5,0,1-(Holi1-TRUNC(Holi1)))+IF(WEEKDAY(Holi2,2)>5,0,Holi2-TRUNC(Holi2))
[Table-It] version 09 by Erik Van Geit