## 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.