844970_en-US

liliconcom

calculates the number of weekdays (Monday to Friday) between two date columns (Start Date and End Date)

Hello Community,

 

I want to create a column formula in JMP 18 that calculates the number of weekdays (Monday to Friday) between two date columns (Start Date and End Date). Weekends (Saturday and Sunday) should be excluded. The formula must be compatible with JMP's column formula editor and should not produce any errors. I don't use JSL

 

Thank you in advance for your support.

Basic Data Analysis and ModelingData Exploration and VisualizationRe: calculates the number of weekdays (Monday to Friday) between two date columns (Start Date and End Date)

Can you provide example table with an example column with correct results?

Re: calculates the number of weekdays (Monday to Friday) between two date columns (Start Date and End Date)

Hi @Christina,

 

Give this a try. Just make sure to change the column names at the bottom to the names you have in your data table.

 

Thanks,

Ben

Names Default to Here(1);
dt = Current Data Table();

//Wipe the old weekday count column if it exists from this script being ran
try(dt:Weekdays Count<<SET selected=""></SET>

 

 

Re: calculates the number of weekdays (Monday to Friday) between two date columns (Start Date and End Date)

Here are few formulas (no idea about their performance). Some slight adjustments might have to be done depending on how you wish to calculate the difference between two dates

Using Summation

 

</CODE></PRE>
<P>Using matrix + sum</P>
<PRE><CODE class=" language-jsl">Sum(1 < Day of week(:start + (0::Date Difference(:start, :end, "day")) * In Days(1)) < 7)Summation(i = 0, Date Difference(:start, :end, "day"),
	!(Day of week(:start + In Days(i)) == 7 | Day of week(:start + In Days(i)) == 1);
)

Example script

View more...

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(365),
	Compress File When Saved(1),
	New Column("start", Numeric, "Continuous", Format("d/m/y", 10), Input Format("d/m/y"), Formula(24Feb2025)),
	New Column("end", Numeric, "Continuous", Format("d/m/y", 10), Input Format("d/m/y"), Formula(24Feb2025 + In Days(Row() - 1))),
	New Column("Column 3",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Formula(
		Summation(i = 0, Date Difference(:start, :end, "day"),
			!(Day of week(:start + In Days(i)) == 7 | Day of week(:start + In Days(i)) == 1);
		)
		)
	),
	New Column("Column 3",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Formula(
			Sum(1 < Day of week(:start + (0::Date Difference(:start, :end, "day")) * In Days(1)) < 7);
		)
	)
);

 

Re: calculates the number of weekdays (Monday to Friday) between two date columns (Start Date and End Date)

Hi,

thank you for the quick reply! With my data, both above mentioned formulas give the same result. I provide some example data. 

 

For example: end date is 31.01.2025, start date is 23.01.2025. The result of both formulas is 7. 

 

That are 7 working days, so thank you also for correct solutions ChatGPT wasn't able to solve that