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

Options
Member

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:

• Coach
Options

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 */
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 */
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 */
/ 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 any users posts that helped you.
• Coach
Options

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 any users posts that helped you.

• Coach
Options

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 */
/ 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
)
```

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Coach
Options

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 */
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 */
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 */
/ 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 any users posts that helped you.
• Member
Options

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
Options

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 any users posts that helped you.
• Member
Options

How would apply the filter to remove weekend’s

• Coach
Options

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.