Business Days
Hello all!
I noticed a post or two discussing that there was a Business Days or Workdays-type function in beta and I am wondering if there are any solutions to finding the number of business days between two dates until this solution goes live?
My data is fairly straight forward; assuming two columns [Date 1] and [Date 2] does anyone have an elegant solution to finding the number of business days between these dates? Business days would be defined here as M-F and if possible (although I know this is asking a lot) excluding certain dates that are holidays (I could add these manually to the function as necessary if someone has a way of incorporating this).
Thank you community!
M
Best Answer
-
Hello @mbelmont,
I have found two beastmode formulas that will accomplish this:5* FLOOR(DATEDIFF(`endDate`, `startDate`) /7) + SUBSTRING('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(`startDate`) + WEEKDAY(`endDate`) + 1, 1)
AND
CASE WHEN YEARWEEK(`End Date`) = YEARWEEK(`Start Date`) THEN ((DATEDIFF(DATE(`End Date`),DATE(`Start Date`)) - (CASE WHEN DAYOFWEEK(`Start Date`) = 1 THEN 1 ELSE 0 END) - (CASE WHEN DAYOFWEEK(`End Date`) = 7 THEN 1 ELSE 0 END))) ELSE ((DATEDIFF(DATE(`End Date`),DATE(`Start Date`)) - DAYOFWEEK(`End Date`)-(7-DAYOFWEEK(`Start Date`)))*2) + DAYOFWEEK(`End Date`) + (7-DAYOFWEEK(`Start Date`)) - (CASE WHEN DAYOFWEEK(`End Date`) = 7 THEN 1 ELSE 0 END) - (CASE WHEN DAYOFWEEK(`Start Date`) = 1 THEN 1 ELSE 0 END)-2 END
Please keep in mind that `End Date` and `Start Date` will need to be replaced with your date columns.
If you have any difficulty implementing this beastmode you can reach out to DOMO's Support team.
You can reach them at the following url https://support.domo.com/**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"4
Answers
-
Hello @mbelmont,
I have found two beastmode formulas that will accomplish this:5* FLOOR(DATEDIFF(`endDate`, `startDate`) /7) + SUBSTRING('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(`startDate`) + WEEKDAY(`endDate`) + 1, 1)
AND
CASE WHEN YEARWEEK(`End Date`) = YEARWEEK(`Start Date`) THEN ((DATEDIFF(DATE(`End Date`),DATE(`Start Date`)) - (CASE WHEN DAYOFWEEK(`Start Date`) = 1 THEN 1 ELSE 0 END) - (CASE WHEN DAYOFWEEK(`End Date`) = 7 THEN 1 ELSE 0 END))) ELSE ((DATEDIFF(DATE(`End Date`),DATE(`Start Date`)) - DAYOFWEEK(`End Date`)-(7-DAYOFWEEK(`Start Date`)))*2) + DAYOFWEEK(`End Date`) + (7-DAYOFWEEK(`Start Date`)) - (CASE WHEN DAYOFWEEK(`End Date`) = 7 THEN 1 ELSE 0 END) - (CASE WHEN DAYOFWEEK(`Start Date`) = 1 THEN 1 ELSE 0 END)-2 END
Please keep in mind that `End Date` and `Start Date` will need to be replaced with your date columns.
If you have any difficulty implementing this beastmode you can reach out to DOMO's Support team.
You can reach them at the following url https://support.domo.com/**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"4 -
Another option is to create a date table that tags days as business days or not. That way you can exclude holidays that your company is closed or whatever criteria you need. The table can be linked to any other table by date and you get a lot of date information that will be consistent. We built our own, but DOMO has one that they share with customers as well.Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.0 -
Thank you so much! Worked perfectly.
0 -
Is there any update on the business days function?
2 -
Neither of these formulas worked properly for me. I am currently in the first week of 2024. Dates from half a year ago are not calculating correctly.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@ArborRose how about something in Magic ETL, leveraging the Domo Dimensions calendar (you could write additional case logic with your own calendar superseding this),
like the following:
CASE
WHEN
holidayDescr IS NOT NULL THEN 0
WHEN
DAYOFWEEK(`dt`) IN (2,3,4,5,6) then 1 else 0
END0 -
Thanks. I found a formula Grant used in a different thread and using my fields it comes out with the correct days.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive