Calculate Business Days Between Two Variable Dates

In problem solving a scenario I found the need to calculate the number of business days between two variable dates. This turns out to be more complex than just some division and multiplication, as either the start or end date (or both) might fall on a weekend. So here is my solution with a guided walkthrough for how I approached the problem.

The Breakdown

Determine the numeric day of the week on which your start and end dates fall using formatDate() (Sun = 0, Mon = 1 … Sat = 6), or use the simplified version day() (Mon = 1, Tue = 2 … Sun = 7). We will use these numbers to calculate the offsets.

day(dateStart(prop("Date")))

day(dateEnd(prop("Date")))

Calculate the total number of days between the start and end dates using dateBetween(), then add 1 to include the start and end dates in the equation.

dateBetween(dateEnd(prop("Date")), dateStart(prop("Date")), "days") + 1

Add the starting date offset to the number of days between, subtract the ending date offset from the number of days between. This brings your weeks in alignment and simulates both dates landing on the first day of the week (Sunday).

+ day(dateStart(prop("Date"))) - day(dateEnd(prop("Date")))

Divide the total number of days by 7 to get the number of weeks, then multiply by 2 (because we know each full week has two non-business days). Subtract this number from the days between.

prop("DaysBetween") - prop("DaysBetween") / 7 * 2

Finally, factor the offsets back in, this time subtracting the starting date offset, and adding the ending date offset.

• If the start date offset is 0 (meaning it is a Sunday), we subtract 1 instead of 0.
• If the end date offset is 6 (meaning it is a Saturday), we add 5 instead of 6.

- if(prop("Offset Start") == 0, 1, prop("Offset Start")) + if(prop("Offset End") == 6, 5, prop("Offset End")))

The result is the calculated number of business days between two variable dates. MATH JUST HAPPENED!

The “Business Days” column uses the “Offset Start”, “Offset End” and “Days Between” properties to calculate the final answer. Having the calculations broken out over multiple properties makes them more manageable to work with. However, here it is combined into one single formula.

The Full Formula

round(dateBetween(dateEnd(prop("Date")), dateStart(prop("Date")), "days") + 1 + day(dateStart(prop("Date"))) - day(dateEnd(prop("Date"))) - (dateBetween(dateEnd(prop("Date")), dateStart(prop("Date")), "days") + 1 + day(dateStart(prop("Date"))) - day(dateEnd(prop("Date")))) / 7 * 2 - if(day(dateStart(prop("Date"))) == 0, 1, day(dateStart(prop("Date")))) + if(day(dateEnd(prop("Date"))) == 6, 5, day(dateEnd(prop("Date")))))

You can duplicate the template below to see a demo of this in action!