tranform a complex formula to a user defined formula

[neveu]

hi,

i have the following formula to calculate the difference in days, hours, minutes, seconds between 2 dates (excluding the weekends and the holydays).

=IF(NETWORKDAYS(A3+1,B3-1)<0,0,NETWORKDAYS(A3+1,B3-1))+IF(WEEKDAY(A3,2)>5,0,1-(A3-TRUNC(A3)))+IF(WEEKDAY(B3,2)>5,0,B3-TRUNC(B3))

the A3 in the formula is the starting date and B3 is the ending date.

i would like to create a user defined formula with this formula so when someone else want's to use it he just have to specify the begining and the ending date ,instead of replacing the A3 and B3 with his cells refference.

i'm not very experienced in writting the vba code.

could someone help me with this?

thank you in advanced
neveu

 

[neveu]

sorry,
i forgot to add some explanaitions realted to the legal holydays in the networkdasy functions below

hi,

i have the following formula to calculate the difference in days, hours, minutes, seconds between 2 dates (excluding the weekends and the holydays).

=IF(NETWORKDAYS(A3+1,B3-1,legal_holydays)<0,0,NETWORKDAYS(A3+1,B3-1,legal_holydays))+IF(WEEKDAY(A3,2)>5,0,1-(A3-TRUNC(A3)))+IF(WEEKDAY(B3,2)>5,0,B3-TRUNC(B3))

the A3 in the formula is the starting date and B3 is the ending date;the legal_holydays is the range containing the specific legal holydays

i would like to create a user defined formula with this formula so when someone else want's to use it he just have to specify the begining the ending date , and to specify the legal holydays instead of replacing the A3 and B3 and legal_holydays with his cells refferences.

i'm not very experienced in writting the vba code.

could someone help me with this?

thank you in advanced
neveu

 

[crimson_b1ade]

you also forgot to mention the holidays of what country. As I'm sure every country has their own little holidays.

Just as an fyi, you may get little or no response on this question since I think it is very difficult (though not impossible) to programmatically come up with holiday dates (especially Easter, which is a whole calculation in and of itself).

The first part of your question (converting native function to a UDF) is not so hard. But why would you do that since a native function does exist (and you are using it). I think it would be less efficient, IMO, to convert to a UDF.

So in short (or long), don't break something that doesn't need fixin'. Or is it don't fix something that ain't broken.

Good Luck!

 

[neveu]

hi crimson,

thank you for your inputs.
the reason why i need this as a UDF is that the colleagues for which i developed this function don't have much excel experience. hence asking them to change the cells reference would confuse them even more. therefore i was looking for a UDF which they can insert using the function wizard.

also i think i found a very simple solution for solving the list of legal holydays: creating a defined range named legal_holydays.

all the best,
enveu

 

[JackBean]

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:

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.

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:

  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

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