Domo Idea Exchange - Beast Modes - Business Days Prior to Today
Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to calculate the number of business days between two dates.
Problem:
How to calculate the number of business days (Monday - Friday) between two dates.
Solution:
We need to calculate the number of days in between the two dates, remove the days which are weekends and an extra day if it's not a Saturday. The logic works because we don't want to count today so we subtract the extra day but because Saturday comes before a Sunday we still need to remove that single day.
Let's break it down into the different steps.
First, get the number of days between the two dates:
-- Author: -- Created: -- Last Modified: -- Description: -- Calculate the number of actual days between the two dates DATEDIFF(CURRENT_DATE(), `dt`)
Next determine the number whole weeks and multiply that number by 2 to subtract from the date difference. We determine the number of whole weeks between the two by calculating the first day of the week for each (This is the same as subtracting the difference of 7 and the day of week for the date)
-- Author: -- Created: -- Last Modified: -- Description: -- Days of weekends between the two dates DATEDIFF(CURRENT_DATE() + INTERVAL (1 - DAYOFWEEK(CURRENT_DATE())) DAY, `dt` + INTERVAL (1 - DAYOFWEEK(`dt`)) DAY) / 7 * 2
Finally we have a condition to check if the start date is a Saturday or not. This is to remove the current date with Saturday being the exception since it precedes Sunday and is a partial week.
-- Author: -- Created: -- Last Modified: -- Description: -- If we don't start on a Saturday remove the current day so we don't count it CASE WHEN DAYOFWEEK(`dt`) <> 7 THEN 1 ELSE 0 END
Finally tying it all together:
-- Author: -- Created: -- Last Modified: -- Description: -- Calculate the number of actual days between the two dates DATEDIFF(CURRENT_DATE(), `dt`) -- Calculate the number of whole weeks: -- Calculate the number of days between the start of the week for both dates - DATEDIFF( -- Get the last of the prior week for our current date CURRENT_DATE() + INTERVAL (1 - DAYOFWEEK(CURRENT_DATE())) DAY, -- Get the last day of the prior week for our start date `dt` + INTERVAL (1 - DAYOFWEEK(`dt`)) DAY ) / 7 * 2 -- Divide by 7 to get the number of whole weeks and multiply it by 2 to get the number of weekend days (Saturday and Sunday) -- If we don't start on a Saturday remove the current day so we don't count today - (CASE WHEN DAYOFWEEK(`dt`) <> 7 THEN 1 ELSE 0 END)
Notes:
This is a simplified calculation of business days. It doesn't take into account holidays or other days your business may be closed.
Also, this will return positive numbers for the past and negative numbers for dates in the future. This can be changed by simply multiplying the final business days number by -1.
**Did this solve your problem? Accept it as a solution!**
Comments
-
Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 60 App Studio
- 41 Variables
- 687 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive