NETWORKDAYS Function

[#1]

This add-in will return the number of whole workdays between two dates and create a formula column containing the result.  The script automatically identifies weekends, however, holidays are read in from the included Holiday List.jmp file. This file currently contains 2016 federal holidays and resides in your Addin Home directory upon installation. To edit the file:

1. Click on the View ==> Add-Ins... menu

2. Select NETWORKDAYS from your registered add-ins list

3. Click on the link to the Home Folder

4. Open the Holiday List.jmp data table and add, delete, or change dates to be excluded

5. Save the file

To run the add-in: Select two date columns in your data table and then select the NETWORKDAYS item from your Add-Ins ==> Custom Functions menu.

Note that the formula column will respond to data changes, however, if a new date is added outside the original min and max dates, the NETWORKDAYS function should be rerun to account for weekend days included in the new range.

Interested in using formulas?  My journal with embedded formulas may interest you.

[john_madden]

Hi Jerry,

Thanks so much for this useful add-in. I'm wondering if you could offer some suggestions, since you've already thought about this.

I need to calculate the duration (in fractional days) between two date/time values excluding weekend periods, i.e. excluding the 48-hour period from 12 am Saturday morning up to 12 am Monday morning.

This NETWORKDAYS function gives me the whole number of workdays, but not the fractional number of workdays, between two date/times. Would it be easy to modify to give the latter?

John

[john_madden]

P.S. Would just adding the "fractional" argument to the two Date Difference functions in the formula do the trick?

[john_madden]

Also, Jerry, what about if for us Saturday counts as a workday, but not Sunday? I'm a novice and I don't understand what in your formula accmplishes the Sat/Sun exclusion.

[jerry_cooper]

Hey John,

Glad you've found the add-in to be useful. In answer to your questions:

1. Yes, adding the "fractional" argument to the Date Difference functions should give you partial days. My cursory testing bore this out, but make sure your results are what you expect ;o).

2. Changing which "weekend" days to exclude is easy, however, this will require modifying the included script. The add-in works by building a matrix of days to exclude - this is called "exclusions". The "exclusions" matrix is comprised of the Holidays and all Saturdays and Sundays between the max and min dates from the two date columns chosen. You can see the actual values for "exclusions" by double-clicking the formula in the formula editor (these are in JMP date format, i.e. # of seconds since January 01, 1904). To change the script to exclude different days of the week, do the following:

a. Select "View->Add-Ins..." from your JMP menu

b. Select NETWORKDAYS from the Registered Add-Ins window

c. Click on the link next to Home Folder to open the folder containing the script

d. Make a backup copy of the NetWorkDays.jsl file (just in case...)

e. Double-click the NetWorkDays.jsl file to enter the script editor

i. Look for the line with the Modulo function, a remainder of 1 means it found a Sunday, a remainder of 0 means it found a Saturday.

ii. Deleting " | Modulo( Day Of Week( mindate ) + i, 7 ) == 0" from the statement will remove Saturday from the exclusions.

iii. Save the script keeping the same name

By the way, you could add the "fractional" argument to the Date Difference functions in the script if you want so you don't have to do this after the fact...



Hope this helps.

-Jerry

[john_madden]

Thanks, Jerry! I will take your suggestions, and I'll let you know if it works out as hoped. This is great.

[john_madden]

I think I've got it working! I had to make a few changes:

1. Add the "fractional" argument to the Date Difference functions.

2. The "+ 1" factor in the formula is not needed anymore, and must be deleted.

3. I removed the Modulo 7 == 0 clause as instructed to keep Saturday as a workday.

4. I noticed that if I include holidays that occur on a Sunday in the holidays list (as did New Years 2017), they get subtracted twice (once for being a Sunday and once for being a holiday), so no Sunday holidays allowed in the list.

With that, I've tried it out on a number of examples and it seems to work correctly.

Thanks again, Jerry!!!!

[rahul_pd]

@jerry_cooper this add-in is very useful and I am using it to calculate average turnaround time for my lab to fulfill the analytical requests. I am wondering if you can offer me some suggestions - Sometime we receive the requests on holidays and for those requests clock starts on next business day. This add-in  counts the submission day in calculation of network day. would it be possible to make some changes to scripts so that it won't calculate the starting day if it falls on one of the holiday?  

[Quastenflosser]

Hi all,

just started with jump and found this information here.

My issue is currently, that i am noit able to open the Jsl script, there is simply no path to where i could find and open the Hioliday jsl file.

Only RThing i can do is install the add on and the run it.

Does anybody have an idea how to access the holidays.jsl within the add on in order to adjust weekend and Holidays for current year and region?

[scott_allen]

Hi @Quastenflosser 

You can locate the JSL file(s) for an add-in by going to the main menu: View > Add-Ins

Select the add-in and then click on the Home Folder: hyperlink. This takes you to the folder that has all the files installed by the add-in.

[MikeD_Anderson]

In addition to @scott_allen's answer - note the instructions indicate you're looking for a holiday list.jmp file (a jmp data table) not a holiday list.jsl file (a script file).  

[Quastenflosser]

dear Scott and mike, thanks for your input!

Need to clarify:

- Current Version used: 

When trying to open Add-ins:

- 

Next, i do not find the ,,Home button" , the button mentioned below just brings me back to the main screen:

I found another option to take a deeper Look into the add-in, but still there is now option to pen the Holiday-information:

1st, open using a distinctive "Add-in"-setting

Then navigate to ,,Additional Data"-Tab, but still, only thing i can do is copy that file, not open it in any way:

[scott_allen]

@Quastenflosser 

Let me try to clarify:

The menu you need to go to is the View menu (on the JMP menu bar) and then select Add-Ins...

When you select "Add-Ins..." you will see a list of all the add-ins you have installed. Select the one you are interested in and click the home menu:

When you click the link, you will go to the folder for the add-in that has all the required files:

In this case, the Holiday List file is called by the add-in. Editing this file will update the holiday schedule in the add-in.

[Quastenflosser]

Great, finally got it!

[Quastenflosser]

Now i seem to be confused about formatting, hopefully you could kindly provide another advice?

Lets say i have the date difference expressed in days (as it is pre configured in the networkdays-add in)

I have the following example:

Date 1 :  2025.01.19T14:15:22   (YYYYMMDD;HHMMSS)

Date 2:   2025.01.20T16:15:22    (YYYYMMDDHHMMSS)

-> The Date Time Difference Formula puts a whole number = 1, which makes sense considering the output being a whole number formatted in days

My issue:

- Even when trying to set number to include two decimals, it puts "1,00" instead of "1,...." something (it is 1 day and 2 hours)

Is it also possible to set the output as a kind of duration? I tried it but ended up in "00:00:00..." depending on the duration format.

Aim: Based on the above mentioned example dates, I'd like to get a value in the format " 1 days 2 hours" (considering network days-addin-formula). But it seems that using date time difference functionality, i cannot get the "1" day into  " 1 day and 2 Hours".

Did i miss something? 

[scott_allen]

The script uses the Date Difference function, which has an Alignment argument. In line 49 and 51 of the script you can see the Date Difference calculation. To get fractional Days, change the function from this:

Date Difference( Expr( thecols[a] ), Expr( thecols[b] ), "Day" )

to this:

Date Difference( Expr( thecols[a] ), Expr( thecols[b] ), "Day", "Fractional" )

Getting this back into a Timestamp requires another change to the formula. Even though the duration is specified as Days, the number is just a number. If you convert this to a duration format, like :day:hr:m, you will see the duration is represented in seconds. So, to convert to days, multiply the value by 86,400 (60*60*24). Now that the column is in days, you can convert to any duration format you would like. I show one possibility here:

colform = Eval Expr(
	exclusions = Expr( wkend );
	If( Expr( thecols[a] ) - Time Of Day( Expr( thecols[a] ) ) <= exclusions[Loc Sorted( exclusions, Expr( thecols[a] ) )],
		Date Difference( Expr( thecols[a] ), Expr( thecols[b] ), "Day", "Fractional" ) + 1 - ( Loc Sorted( exclusions, Expr( thecols[b] ) )
		- Loc Sorted( exclusions, Expr( thecols[a] ) ) + 1 ),
		Date Difference( Expr( thecols[a] ), Expr( thecols[b] ), "Day", "Fractional" ) + 1 - Loc Sorted( exclusions, Expr( thecols[b] ) )
		+Loc Sorted( exclusions, Expr( thecols[a] ) )
	)*86400; // here is the adjustment to convert from seconds to days
);

dt << New Column( "NETWORKDAYS (" || Eval( thecols[a] << Get Name ) || " to " || Eval( thecols[b] << Get Name ) || ")",
	Formula( Name Expr( colform ) ), Format( ":day:hr:m", 14 ), Input Format( ":day:hr:m" ) // here is the duration format for the new column
);

The first duration column has only the fractional duration. The second has the adjustments and duration format.

[Quastenflosser]

Dear Scott, 

much appreciate your clear explanation. I will try and test this one