Calculate time between two dates during business hours, excluding holidays & Sat/Sun
Need to calculate the elapsed time/minutes between Start Date and End Date, using business hours of 9:00AM to 5:00PM, excluding Saturday/Sunday & holidays. Anyone able to help? either in Magic ETL or Beast Mode.
Answers
-
You might need to tweak this a little bit, but this is what I put together for someone else who had a similar question in the Dojo.
/* check to see if the start hour is too late */ (CASE WHEN HOUR(`startdate`) >= 17 THEN ((UNIX_TIMESTAMP(`enddate`) - UNIX_TIMESTAMP( /*move it to the next day and start at 9 */ ADDTIME(ADDDATE(DATE(`startdate`), interval 1 day),9*60*60))) / 3600) ELSE /* subtract and then divide by 3600 to get the hours */ ((UNIX_TIMESTAMP(`enddate`) - UNIX_TIMESTAMP(`startdate`)) / 3600) /* if there is a difference in days, subtract 16 hours per day */ - (DATEDIFF(`enddate`,`startdate`)*16) END )
You can see the full thread here: https://dojo.domo.com/discussion/54212/difference-in-business-hours
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
For your situation, I would recommend doing this in Magic ETL as it will be a lot easier to manage because you can break things down to into smaller chunks with multiple formulas. For example, I would created a formula called AdjustedStartDate that looks like this:
/* check to see if the start hour is too early */ (CASE WHEN HOUR(`startdate`) <= 9 THEN /*move it to start at 9 */ ADDTIME(DATE(`startdate`),9*60*60) ELSE `startdate` END)
I would then created another formula called AdjustedEndDate that looks like this:
/* check to see if the start hour is too early */ (CASE WHEN HOUR(`enddate`) >= 17 THEN /*move it to 5pm */ ADDTIME(DATE(`enddate`),17*60*60) ELSE `enddate` END)
You can then do a straightforward formula to get the hours like this:
/* subtract and then divide by 3600 to get the hours */ ((UNIX_TIMESTAMP(`adjustedenddate`) - UNIX_TIMESTAMP(`adjustedstartdate`)) / 3600)
To handle the Monday - Friday piece, you might use a filter tile and use the DAYOFWEEK() function to filter to days 2-6.
Hope all of this makes sense.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
Thank you for looking into my question, I just need to confirm if your recommendation exclude holidays as well? If not, pls advise how to exclude holidays in the calculation.
0 -
To exclude holidays, if your company follows standard US holidays, you can add the Domo Dimensions Calendar dataset, filter to isHoliday = 1 and then left join your main table to this dataset and then filter out any entries where there is a match.
If your company has a different set of holidays, you would need to upload that list and follow the rest of the steps.
Hope that makes sense.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
How would apply the filter to remove weekend’s
0 -
Use a formula filter and exclude the
dw
field in the Calendar dataset where the value is 1 (Sunday) or 7 (Saturday)`dw` NOT IN (1,7)
Alternatively you can do two filter criteria separately to make sure dw doesn't equal 1 and dw doesn't equal 7.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 463 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 189 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 172 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive