Hours between 2 dates excluding non working hours/weekends/

[Infidti]

Hi there, 

I am trying to find a simple way to calculate the hours difference between two dates. But I need to exclude bank holidays and weekends but also the calculation can only sum the hours in-between specified working hours. If something comes through outside of these working hours the sum would only start the next day at the working hour start time. I have seen loads of diff calculations but none seem to work the way I want. I have managed to a calculation to work but doesn't take into account stuff that comes outside working hours. Any help would be amazing

[Anonymous · Accepted solution]

Hi  @Infidti ,

Here are the steps you can follow：

1. Enter data -- create calculated table.

Create a table to hold the dates of Bank Holidays

2. Set the working hours and multiply the working hours by the number of working days.

TIME(9,0,0) -- TIME(17,0,0)

3. Create measure.

Measure =
var _worktime=
DATEDIFF(
TIME(9,0,0),TIME(17,0,0),HOUR)
var _count=
COUNTX(FILTER(
    ALL('Table'),
    NOT('Table'[Date]) in SELECTCOLUMNS('Holiday',"holiday",[bank holidays]) &&
 NOT( WEEKDAY('Table'[Date],2)) in {6,7}),[Date])
return
_count * _worktime

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Infidti , refer if this can help

https://exceleratorbi.com.au/calculating-business-hours-using-dax/