# DP22 - Using Beast Mode to Build Data Storytelling - Adding Business Days

Coach

This post is part of a series of beast modes I presented on during my “Using Beast Mode to Build Data Storytelling” Domopalooza 2022 session. If you find this post helpful please make sure to give it a like or an awesome below.

Use Case

When evaluating different carriers for performance we needed a simplistic calculation to quickly determine how often they were delivering packages on time. The delivery days promised by the carriers are in business days however the days an order was shipped and when it was delivered are actual calendar days. This beast mode allows us to add a specific number of business days to a date to compare to when a delivery was made. This calculation assumes a work week of Monday through Friday and doesn’t consider the days in which the business was closed (for example holidays).

Beast Mode

````Ship Time` +
INTERVAL (
FLOOR(`Estimated Delivery Days`/5) * 7
+
CASE WHEN MOD(`Estimated Delivery Days`,5) + DAYOFWEEK(`Ship Time`) >= 7 THEN
2 - FLOOR(DAYOFWEEK(`Ship Time`)/7)
ELSE
0
END
+
MOD(`Estimated Delivery Days`,5)
) DAY
```

Beast Mode Breakdown

```FLOOR(`Estimated Delivery Days`/5) * 7
```

``Estimated Delivery Days`/5` converts the business days into calendar weeks as there are 5 business days per week (Monday - Friday).

`FLOOR` returns the whole number rounded down to get the number of full weeks. Partial weeks will be added later.

Multiply by 7 to convert to calendar days

```DAYOFWEEK(`Ship Time`)
```

DAYOFWEEK takes a date and returns a value for the number of the day of the week (1 – Sunday … 7 – Saturday)

```MOD(`Estimated Delivery Days`,5)
```

This is the number of business days left in the partial business week

```CASE WHEN MOD(`Estimated Delivery Days`,5) + DAYOFWEEK(`Ship Time`) >= 7 THEN
```

Putting these both into In simpler terms - Does the partial week cause us to go into the weekend (beyond Saturday / day 7)?

```FLOOR(DAYOFWEEK(`Ship Time`)/7)
```

Returns 0 for Sunday – Friday, 1 for Saturday. `DAYOFWEEK` will return 1-6 Sunday-Friday and 7 for Saturday. Dividing this by 7 returns a number < 1 for Sunday-Friday and 1 for Saturday. `FLOOR` converts any decimal less than 1 to be 0 by dropping the decimals. This is an alternative way to do

```CASE WHEN DAYOFWEEK(`Ship Time`) = 7 THEN 1 ELSE 0 END
```

```2 - FLOOR(DAYOFWEEK(`Ship Time`)/7)
```

If the start date was Saturday only add one day to get throught the weekend instead of the standard 2 days

```MOD(`Estimated Delivery Days`,5)
```

Use the `MOD` function to divide the estimated business days we’re adding and get the remaining to add to the calendar date.

Final Result

Bonus - On Time Deliveries

We can take this one step further and use another `CASE` statement to bucket our deliveries into either On-Time or Late

```CASE WHEN `Delivery Time` <= (`Ship Time` +
INTERVAL (
FLOOR(`Estimated Delivery Days`/5) * 7
+
CASE WHEN MOD(`Estimated Delivery Days`,5) + DAYOFWEEK(`Ship Time`) >= 7 THEN
2 - FLOOR(DAYOFWEEK(`Ship Time`)/7)
ELSE
0
END
+
MOD(`Estimated Delivery Days`,5)
) DAY)

THEN 'On-Time'
ELSE 'Late'
END
```