Calculating the number of business days between two dates may seem rudimentary, but anyone attempting this task will quickly realize how lengthy this calculation can be. While many software applications like Microsoft Excel have built in workday formulas, not all programs have one. In this post, I will provide an efficient algorithm for calculating the number of workdays between two dates while demonstrating the use of the algorithm in Microsoft Excel and the popular business intelligence tool, Domo.
The Algorithm
Software applications can perform mathematical calculations on dates by encoding each date as the number of days since a certain point in time. For example, if you enter the date “9/20/2023” in Microsoft Excel and then convert the date to a number, you will get the number 45,189. This is in fact the number of days since 1/1/1900. To further support this observation, try typing a date before the year 1900 in Excel and you will notice the program doesn’t even recognize it as a date! By encoding dates as numbers, software applications can add and subtract dates with ease. Additionally, because every date is a number, it is very easy to determine which day of the week a date falls on. Assuming 1/1/1900 is the start date, dividing every Sunday by seven will result in a remainder of one, dividing every Monday by seven will result in a remainder of two, and so on… Saturday is the only day of the week perfectly divisible by seven. Essentially, Sunday through Saturday is nothing more than a repetitive pattern of 1, 2, 3, 4, 5, 6, and 7. This information will be valuable for understanding how the the following algorithm works.
The first step in constructing the algorithm is to acknowledge that any seven-day period has exactly five workdays in it. With this understanding, it can then be said if the difference between any two dates, d, is greater than or equal to seven, then the number of workdays between those two dates, n, will always be greater than or equal to five times the number of times seven divides into d. Therefore, the challenge is finding the number of workdays in the remainder after dividing d by seven.
Let’s create a few variables to make the algorithm easier to read:
n = number of workdays between start date and end date
s = weekday of start date (e.g. Sunday = 1)
e = weekday of end date
d = number of days between start date and end date
STEP 1:
we’ll create a variable, x, which will represent the floor of the difference in the start date and end date divided by seven, times five:
x = ⌊d / 7 ⌋ * 5
STEP 2:
we’ll create a variable, z, which will represent the output of the following formula:
z = (if s = 1, then 5 else 7 – s) + (if e = 1, then 5 else e – 1)
STEP 3:
Putting it all together, the final formula will be as follows:
n = (if s = e, then -5 else if z > 5, then -5 else 0) + z + x
Testing the workday algorithm in Microsoft Excel
To test the algorithm above, I have created a small table in Excel with start dates and end dates. In the first column directly adjacent to the end date column, I have used Excel’s NETWORKDAYS formula to compute the number of workdays between the two dates. The fourth column contains the workday algorithm. Below is the algorithm formatted for Excel:
=IF(WEEKDAY(A1)=WEEKDAY(B1),-5,IF(IF(WEEKDAY(A1)=1,5,7-WEEKDAY(A1))+IF(WEEKDAY(B1)=7,5,WEEKDAY(B1)-1)>5,-5,0))+IF(WEEKDAY(A1)=1,5,7-WEEKDAY(A1))+IF(WEEKDAY(B1)=7,5,WEEKDAY(B1)-1)+FLOOR.MATH((B1-A1)/7)*5
The workday algorithm produces equivalent results to the built-in NETWORKDAYS formula.
Using the workday algorithm in Domo
Domo is a popular business intelligence tool used to create business dashboards. The tool allows you to perform columnar operations, but does not have a NETWORKDAYS formula like Excel. This is an example of how valuable the workday algorithm can be for software applications that do not have built in formulas for performing this calculation. Below is the algorithm formatted for Domo:
CASE WHEN WEEKDAY(start) = WEEKDAY(end) THEN -5 WHEN ((CASE WHEN WEEKDAY(start) = 1 THEN 5 ELSE (7 – WEEKDAY(start)) END) + (CASE WHEN WEEKDAY(end) = 7 THEN 5 ELSE (WEEKDAY(end) – 1) END)) > 5 THEN -5 ELSE 0 END + ((CASE WHEN WEEKDAY(start) = 1 THEN 5 ELSE (7 – WEEKDAY(start)) END) + (CASE WHEN WEEKDAY(end) = 7 THEN 5 ELSE (WEEKDAY(end) – 1) END)) + FLOOR(DATEDIFF(end, start)/7)*5
The formula in Domo produces the same results we obtained in Excel.
