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

guanlafe
guanlafe 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:

Best Answers

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

    /* 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
    MarkSnodgrass 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

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

    /* 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
    MarkSnodgrass 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:

    /* 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.
  • guanlafe
    guanlafe 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.

  • MarkSnodgrass
    MarkSnodgrass 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.
  • Devin_Garrison
    Devin_Garrison Member
    https://dojo.domo.com/main/discussion/comment/57049#Comment_57049

    How would apply the filter to remove weekend’s

  • GrantSmith
    GrantSmith Coach

    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!**

Categories