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

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

Christina — Fri, 28 Feb 2025 13:27:58 GMT

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.

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

jthi — Fri, 28 Feb 2025 13:50:48 GMT

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)

Ben_BarrIngh — Fri, 28 Feb 2025 14:25:33 GMT

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;dt<<delete columns(););
// Add a new column to store the number of weekdays
dt << New Column("Weekdays Count", Numeric, "Continuous");

// Define a function to calculate weekdays between two dates
calcWeekdays = Function({startDate, endDate},
    {default local = 1},
    weekdays = 0;
    startDate = Date MDY(Month(startDate), Day(startDate), Year(startDate));
    endDate = Date MDY(Month(endDate), Day(endDate), Year(endDate));
    for(i = startDate, i <= endDate, i++,
        if(Day Of Week(i) != 1 & Day Of Week(i) != 7, // Exclude Sundays (1) and Saturdays (7)
            weekdays++;
        );
    );
    weekdays;
);

// Apply the function to each row - Convert to days with 86400 - //MAKE SURE TO CHANGE THE COLUMN NAMES HERE TO THE CORRECT TYPE
For Each Row(
    :Weekdays Count = (calcWeekdays(:Start Date, :End Date)/86400);
);

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

jthi — Fri, 28 Feb 2025 14:37:55 GMT

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

Summation(i = 0, Date Difference(:start, :end, "day"),
	!(Day of week(:start + In Days(i)) == 7 | Day of week(:start + In Days(i)) == 1);
)

Using matrix + sum

Sum(1 < Day of week(:start + (0::Date Difference(:start, :end, "day")) * In Days(1)) < 7)

Example script

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)

Christina — Mon, 03 Mar 2025 17:11:15 GMT

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