topic Re: How to calculate number of working days? in Discussions

How to calculate number of working days?

Martinej — Sat, 10 Jun 2023 23:18:41 GMT

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

Re: How to calculate number of working days?

Jeff_Perkinson — Mon, 07 Sep 2020 14:11:44 GMT

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

Re: How to calculate number of working days?

Craige_Hales — Mon, 07 Sep 2020 22:40:32 GMT

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

Re: How to calculate number of working days?

Martinej — Wed, 09 Sep 2020 08:34:50 GMT

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

any suggestions?

Re: How to calculate number of working days?

Craige_Hales — Wed, 09 Sep 2020 14:30:05 GMT

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 )

Re: How to calculate number of working days?

Martinej — Fri, 11 Sep 2020 13:34:50 GMT

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.

Re: How to calculate number of working days?

P_Bartell — Sat, 12 Sep 2020 17:20:45 GMT

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.

Re: How to calculate number of working days?

Craige_Hales — Sat, 12 Sep 2020 17:34:26 GMT

My favorite is the old North Carolina baseball holiday.

Re: How to calculate number of working days?

jerry_cooper — Mon, 14 Sep 2020 12:58:05 GMT

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:

Hope this helps,

Jerry

Re: How to calculate number of working days?

Martinej — Thu, 17 Sep 2020 14:06:42 GMT

Hi Jerry

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

Thanks

Martin