Skip to content

    • Charting

    Charting

    Calculate time between two dates during business hours, excluding holidays & Sat/Sun

    Member
    in Charting

    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.

    Tagged:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Coach
      edited March 2022 Answer ✓

      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:

      1. /* check to see if the start hour is too early */
      2. (CASE WHEN HOUR(`startdate`) <= 9 THEN
      3.  /*move it to start at 9 */
      4.  	ADDTIME(DATE(`startdate`),9*60*60)
      5. ELSE
      6. `startdate`
      7. END)

      I would then created another formula called AdjustedEndDate that looks like this:

      1. /* check to see if the start hour is too early */
      2. (CASE WHEN HOUR(`enddate`) >= 17 THEN
      3.  /*move it to 5pm */
      4.  	ADDTIME(DATE(`enddate`),17*60*60)
      5. ELSE
      6. `enddate`
      7. END)

      You can then do a straightforward formula to get the hours like this:

      1.  /* subtract and then divide by 3600 to get the hours */
      2. ((UNIX_TIMESTAMP(`adjustedenddate`) - UNIX_TIMESTAMP(`adjustedstartdate`))
      3. / 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 to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.
    • Coach
      Answer ✓

      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 to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.

    Answers

    • Coach

      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.

      1. /* check to see if the start hour is too late */
      2. (CASE WHEN HOUR(`startdate`) >= 17 THEN
      3. ((UNIX_TIMESTAMP(`enddate`) - UNIX_TIMESTAMP(
      4.  /*move it to the next day and start at 9 */
      5.  	ADDTIME(ADDDATE(DATE(`startdate`), interval 1 day),9*60*60)))
      6. / 3600)
      7. ELSE
      8.  /* subtract and then divide by 3600 to get the hours */
      9. ((UNIX_TIMESTAMP(`enddate`) - UNIX_TIMESTAMP(`startdate`))
      10. / 3600)
      11.  /* if there is a difference in days, subtract 16 hours per day */
      12.  -
      13. (DATEDIFF(`enddate`,`startdate`)*16)
      14. END
      15. )

      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 to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.
    • Coach
      edited March 2022 Answer ✓

      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:

      1. /* check to see if the start hour is too early */
      2. (CASE WHEN HOUR(`startdate`) <= 9 THEN
      3.  /*move it to start at 9 */
      4.  	ADDTIME(DATE(`startdate`),9*60*60)
      5. ELSE
      6. `startdate`
      7. END)

      I would then created another formula called AdjustedEndDate that looks like this:

      1. /* check to see if the start hour is too early */
      2. (CASE WHEN HOUR(`enddate`) >= 17 THEN
      3.  /*move it to 5pm */
      4.  	ADDTIME(DATE(`enddate`),17*60*60)
      5. ELSE
      6. `enddate`
      7. END)

      You can then do a straightforward formula to get the hours like this:

      1.  /* subtract and then divide by 3600 to get the hours */
      2. ((UNIX_TIMESTAMP(`adjustedenddate`) - UNIX_TIMESTAMP(`adjustedstartdate`))
      3. / 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 to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.
    • Member

      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.

    • Coach
      Answer ✓

      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 to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.
    • Member
      https://dojo.domo.com/main/discussion/comment/57049#Comment_57049

      How would apply the filter to remove weekend’s

    • Coach

      Use a formula filter and exclude the dw field in the Calendar dataset where the value is 1 (Sunday) or 7 (Saturday)

      1. `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!**

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories