Working time between two dates and times excluding lunch-break, weekends, holidays

[IsakCL]

Hi, 

I have two datesfield with times. I want to pick up the numbers of hours between Start and Finish excluding lunchbreak, weekends and holidays. 

Start	Finish
2022-01-11 08:00:00	2022-01-13 15:00:00

Working hours between 07:00:00 - 16:00:00

Break between 11:00:00-12:00:00

[v-zhangti · Accepted solution]

Hi, @IsakCL 

You can try the following methods.

New Table:

Date = CALENDAR(MIN('Table'[Start]),MAX('Table'[Finish]))

Column:

Commuting time = [Date]&" "&TIME(7,0,0)

Off duty time = [Date]&" "&TIME(16,0,0)

Measure:

interval = 
DATEDIFF (
    SELECTEDVALUE ( 'Table'[Start] ),
    SELECTEDVALUE ( 'Table'[Finish] ),
    HOUR
)
    - (
        24
            - HOUR ( MIN ( 'Date'[Off duty time] ) - MIN ( 'Date'[Commuting time] ) )
    ) * 2 - 3

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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