How to calculate number of working days?

[#1]

Hi community

I need your help and advise once again. As a new user I'm struggling to learn.

I have a data table with start date and end date. I would like to calculate the number of working days in between the start and end date ?? How is the best way to do that in JMP ?? My idea is to add a column and make a formula, e.g. Date difference?? But i'm not sure what would be the better option.

please see attached example

Thanks

Martin

[#2]

The first thing you need to do is define "working day." Is it every weekday (Mon-Fri)? What about bank/government holidays? Religious holidays?

[#3]

Other questions too: JMP dates always include a time, often set to zero seconds after midnight. But if the range is noon Monday to noon Monday 7 days later, how do you want to count the two half-Mondays? Or even if it is midnight at the start of Monday, should both be counted?

Counting the days might not be a bad choice if your ranges are small and not too many (poorly tested code follows):

// define an expression that answers the question
includeThisDate = Expr(
	2/*monday*/<= Day Of Week( date ) <= 6/*friday*/
);

// brute force counting:
countSelectedDaysInRange = Function( {firstDate, lastDate, testexpr},
	{date, count},
	If( lastDate < firstDate,
		Throw( "dates reversed" )
	);
	count = 0;
	For( date = firstDate, date <= lastDate, date += In Days( 1 ),
		count += (testexpr != 0)
	);
	count;
);

Show( 262 == countSelectedDaysInRange( 1jan2020, 31dec2020, Name Expr( includeThisDate ) ) );
Show( 104 == countSelectedDaysInRange( 1jan2020, 31dec2020, Expr( !(2/*monday*/<= Day Of Week( date ) <= 6/*friday*/) ) ) );
Show( 366 == countSelectedDaysInRange( 1jan2020, 31dec2020, 1 ) );// 2020 is leap year
Show( 0 == countSelectedDaysInRange( 1jan2020, 31dec2020, 0 ) );// no days counted

262 == countSelectedDaysInRange(01Jan2020, 31Dec2020, Name Expr(includeThisDate)) = 1;
104 == countSelectedDaysInRange(01Jan2020, 31Dec2020, Expr(!(2 <= Day Of Week(date) <= 6))) = 1;
366 == countSelectedDaysInRange(01Jan2020, 31Dec2020, 1) = 1;
0 == countSelectedDaysInRange(01Jan2020, 31Dec2020, 0) = 1;

The code above includes the last day (<= lastdate rather than <lastdate) . It starts at the first datetime value and increments by 24 hours and adds one to the count if that datetime value satisfies the expression.

From painful experience I know working with dates and times is hard to get right.

Use the builtin functions as much as possible.

Comment the code for whoever maintains it next.

Test your code carefully!

Also, if you only need a crude approximation,

(1jan2021-1jan2020)/indays(1)*5/7 == 261.428571428571

which is close to the 262 count. 

edit: date difference(1jan2020,1jan2021,"day")*5/7 might look simpler for the crude answer

[#4]

yes working days is every weekday, (mon-fri) it would be best if i can also adjust for local holidays

any suggestions?

[#5]

The simple solution would be to extend the test expression.

// define an expression that answers the question

holidays = associativearray({1jan2020,25dec2020});

includeThisDate = Expr(
	(2/*monday*/<= Day Of Week( date ) <= 6/*friday*/) 
	&
	!(holidays<<contains(date-timeofday(date)))
);

// brute force counting:
countSelectedDaysInRange = Function( {firstDate, lastDate, testexpr},
	{date, count},
	If( lastDate < firstDate,
		Throw( "dates reversed" )
	);
	count = 0;
	For( date = firstDate, date <= lastDate, date += In Days( 1 ),
		count += (testexpr != 0)
	);
	count;
);

Show( 260 == countSelectedDaysInRange( 1jan2020, 31dec2020, Name Expr( includeThisDate ) ) );

New Years and Christmas fall on weekdays in 2020, so 260 rather than 262. You'll need to update the associative array sometime in the future, and you might need historical data as well. Some holidays don't fall on the same day every year. What an ugly problem, which leads to...

A complicated solution might use something like https://www.abstractapi.com/holidays-api 

(Credit to @Jeff_Perkinson  for Changing a date and time column to yield only the date. )

[#6]

working with coding is new to me and i don't seem to get it to work. however i have played around with some of the standard formulas and found a solutions. that however use calendar days and not working days only. For this experiment it is good enough as along as it is the same for comparison.

[#7]

To add a bit to @Craige_Hales's and @Jeff_Perkinson's thoughts around holidays etc., you mentioned thoughts around accounting for 'local holidays'. That gets really difficult unless you adopt some standard definition for 'local'. For example, Canada has a 'Thanksgiving' holiday, just like the US. Except it's a different day on the calendar for Canada compared to the US. And don't even get me started at a city or state level in the US. For example, in the states of Maine and Massachusetts, Patriot's Day is a holiday, not celebrated anywhere else in the US. To make it even more sticky, some businesses close...others remain open. Banks and financial institutions included.

[#8]

My favorite is the old North Carolina baseball holiday. 

[#9 · Accepted solution]

Hi @Martinej ,

I wrote an add-in awhile back that calculates the number of working days between two date columns. Holidays and any other specific days you want to exclude are addressed by maintaining a Holiday List.jmp file stored in your add-in directory. Note: this file could be any document (Excel, .txt, .csv, etc) maintained in any location accessible by the JMP user's machine, however, this would require a minor tweak to the underlying JSL to provide the file location. Otherwise, I believe it addresses your requested need as-is. Follow the instructions for downloading, installing, and editing the Holiday List file:NETWORKDAYS Function 

Hope this helps,

Jerry

[#10]

Hi Jerry

this was really helpfull. I made my own list and now it showed what i would like.

Thanks

Martin