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

guanlafe

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.

MarkSnodgrass

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.

MarkSnodgrass

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.

MarkSnodgrass

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

MarkSnodgrass

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.

guanlafe

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

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.

Devin_Garrison

https://dojo.domo.com/main/discussion/comment/57049#Comment_57049

How would apply the filter to remove weekend’s

GrantSmith

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.