# 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