Subscribe Bookmark RSS Feed
jerry_cooper

Staff

Joined:

Jul 10, 2014

NETWORKDAYS Function

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.

Comments
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!!!!

Article Labels
Article Tags