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

[#1]

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.

[#2]

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

[#3 · Accepted solution]

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

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);
		)
	)
);

[#4]

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

[#5]

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);
);