Calculate working time between 2 timestamp in difference day

[Anonymous]

Hello, 

I'm newbie here please let me know if i miss anything.

I want to calculate working time (minute) between 2 timestamps

but not count time if it not in working hour (9.00 - 18.00)

the data have both finish in same day and finish next day.

If Time A is over 18.00, not count and let start counting on next day till time B

or if Time A start before 9.00, not count and let start counting on 9.00 onward till time B

or if Time A and Time B are in different day, need it to count till 18.00 and break then start counting next day 9.00

Time A	Time B	Processing time (Min)
27-06-22 16:32	28-06-22 9:28
27-06-22 16:32	28-06-22 11:28
27-06-22 16:32	28-06-22 0:35
28-06-22 15:13	29-06-22 14:52
30-06-22 11:48	30-06-22 14:10
01-07-22 13:23	01-07-22 13:47
01-07-22 18:11	01-07-22 15:47

I try to use IF and DATEDIFF function in many ways but still can't calculate it correctly 

Very appreciate if someone can help

Thank you

[Anonymous]

Hi @v-rzhou-msft 

It worked totally Perfect!

I'm really appreciated and Thank you very much your help Master.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I suggest you to try this code to create a calcualted column.

Processing time (Min) = 
VAR _DAYDIFF =
    DATEDIFF ( 'Table'[Time A], 'Table'[Time B], DAY )
VAR _REST_MIN = ( 6 + 9 ) * 60
VAR _STARTDATE =
    IF (
        TIMEVALUE ( 'Table'[Time A] ) < TIME ( 9, 0, 0 ),
        DATEVALUE ( 'Table'[Time A] ) + TIME ( 9, 0, 0 ),
        IF (
            TIMEVALUE ( 'Table'[Time A] ) > TIME ( 18, 0, 0 ),
            DATEVALUE ( 'Table'[Time A] ) + TIME ( 18, 0, 0 ),
            'Table'[Time A]
        )
    )
VAR _ENDDATE =
    IF (
        TIMEVALUE ( 'Table'[Time B] ) < TIME ( 9, 0, 0 ),
        DATEVALUE ( 'Table'[Time B] ) + TIME ( 9, 0, 0 ),
        IF (
            TIMEVALUE ( 'Table'[Time B] ) > TIME ( 18, 0, 0 ),
            DATEVALUE ( 'Table'[Time B] ) + TIME ( 18, 0, 0 ),
            'Table'[Time B]
        )
    )
VAR _PROCESSTIME1 =
    DATEDIFF ( _STARTDATE, _ENDDATE, MINUTE )
RETURN
    _PROCESSTIME1 - _REST_MIN * _DAYDIFF

Result is as below.

Best Regards,
Rico Zhou

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