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?