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.

Best Answers

  • MarkSnodgrass
    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:

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

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

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

  • MarkSnodgrass
    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.
  • 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!**