Calculating the number of weekdays between two dates

Use-case:

I have two date fields (i.e. datestart and dateend) in my dataset that I need to subtract to determine the days between them, but only those that are weekdays.

Calculation and Walkthrough:

Full Calculation:

datediff([dateend],[datestart]) - (
(floor(datediff([dateend],[datestart]) / 7) * 2) + 
CASE WHEN dayofweek([datestart]) - dayofweek([dateend]) IN (1, 2, 3, 4, 5) AND dayofweek([dateend]) != 1 THEN 2 ELSE 0 END + 
CASE WHEN dayofweek([datestart]) != 1 AND dayofweek([dateend]) = 1 THEN 1 ELSE 0 END + 
CASE WHEN dayofweek([datestart]) = 1 AND dayofweek([dateend]) != 1 THEN 1 ELSE 0 END
)

Calculation walkthrough:

Step 1: Find the total number of days between the end and start including the weekend days

datediff([dateend],[datestart])

Step 2: Calculate the total number of full weeks between the two dates, and then multiply by 2 to account for the rough number of weekend days in those weeks. The Floor function will force any partial weeks to be rounded down.

floor(datediff([dateend],[datestart]) / 7) * 2)

Step 3: Calculate adjustment for full weekends within partial weeks. If there’s a partial week, and the end date doesn’t end on a Sunday, then we can assume there’s an additional 2 weekend days we need to subtract out.

CASE WHEN dayofweek([datestart]) - dayofweek([dateend]) IN (1, 2, 3, 4, 5) AND dayofweek([dateend]) != 1 THEN 2 ELSE 0 END

Step 4: Calculate adjustment for end dates that occur on a Sunday. This adjustment is used to subtract an extra day if the end date is a Sunday.

CASE WHEN dayofweek([datestart]) != 1 AND dayofweek([dateend]) = 1 THEN 1 ELSE 0 END

Step 5: Calculate adjustment for start dates that occur on a Sunday. This adjustment is used to subtract an extra day if the start date is a Sunday.

CASE WHEN dayofweek([datestart]) = 1 AND dayofweek([dateend]) != 1 THEN 1 ELSE 0 END

Step 6: Subtract the total number of weekend days (Step 2) and adjustments from Steps 3,4, and 5 from the total number of days between the end and start date.

datediff([dateend],[datestart]) -
(
(floor(datediff([dateend],[datestart]) / 7) * 2) + 
CASE WHEN dayofweek([datestart]) - dayofweek([dateend]) IN (1, 2, 3, 4, 5) AND dayofweek([dateend]) != 1 THEN 2 ELSE 0 END + 
CASE WHEN dayofweek([datestart]) != 1 AND dayofweek([dateend]) = 1 THEN 1 ELSE 0 END + 
CASE WHEN dayofweek([datestart]) = 1 AND dayofweek([dateend]) != 1 THEN 1 ELSE 0 END
)

Visual walkthrough of calculation:

To illustrate how the above calculation works, I’ve included a snapshot of a table that shows an example of 10 different date calculations that use this formula to calculate the total number of week days between each set of dates.

2 Likes