Difference in Business Hours

Mario · January 2022

I'm looking to get the difference in 'business' hours between two date/time fields. The business hour range is 9:00 - 17:00 (or 9am - 5pm), within weekdays.

Additionally, If a user enters a Start Date after 17:00, the start date should get pushed to 9:00 of the next business day.

For example, the following should have an output of 11 hours.

Start: 01/04/2022 11:00

END: 01/05/2022 14:00

I'm guessing this has been encountered quite a few times in the past, can anyone share what they've put together, even if it is just a starting point where I can adjust to my dataflow?

I am looking to perform this within a Beastmode.

I attempted using the solution within the topic listed below but am getting inconsistent results. https://dojo.domo.com/discussion/10059/how-can-i-show-an-age-between-2-time-stamps-in-business-hours

MarkSnodgrass · January 2022

This is an interesting one. I started sketching something out and there are a lot of scenarios to account for. I think you will need multiple nested case statements and you would use the WEEKDAY(), DATEDIFF(), and HOUR() functions to help with doing the math. I'll see if I can come up with something workable over the weekend. If you haven't already looked at this post, this may help you as well.

https://dojo.domo.com/discussion/52686/domo-idea-exchange-beast-modes-business-days-prior-to-today#latest

MarkSnodgrass · January 2022

I think I have something for you to get your started.

/* 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 will need to add some additional case statements to deal with weekends by using the WHEN WEEKDAY(startdate) IN (1,7) which is Sunday and Saturday as I wasn't sure how you want to handle those. Hopefully the above makes sense. For a deeper dive into unix_timestamp, you might want to look at this post.

https://dojo.domo.com/discussion/52682/domo-ideas-conference-beast-modes-time-difference-formatting

With my testing this is what the above beast mode produces.

Hope this helps.

GrantSmith · January 2022

Those are some nice links you have there @MarkSnodgrass :)

You can leverage the logic outlined in the links he's provided to calculate the difference in the number of full days in between your two days, then multiply those days by the number of hours in your day (8) then add in the number of partial days (hours) from your start (17 (5pm) - greater(hour of start or 9am) and end dates (least of 5pm or hour of end time) - 8 (8am)

MarkSnodgrass · January 2022

@Mario how did this go for you? Did this solve your problem?

lb1234 · December 2022

@MarkSnodgrass what would this beastmode look like if you were to only get the business hours for Monday - Friday, not every day?

Difference in Business Hours

Answers

Categories