Difference in Business Hours

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Indiana 🥷
    edited January 24

    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)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.