cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NawaphonTH
New Member

Calculate working time between 2 timestamp in difference day

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 ATime BProcessing time (Min)
27-06-22 16:3228-06-22 9:28 
27-06-22 16:3228-06-22 11:28 
27-06-22 16:3228-06-22 0:35 
28-06-22 15:1329-06-22 14:52 
30-06-22 11:4830-06-22 14:10 
01-07-22 13:2301-07-22 13:47 
01-07-22 18:1101-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

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @NawaphonTH ,

 

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.

RicoZhou_0-1664433697179.png

 

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.

View solution in original post

2 REPLIES 2
NawaphonTH
New Member

Hi @RicoZhou 

It worked totally Perfect!

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

RicoZhou
Community Support
Community Support

Hi @NawaphonTH ,

 

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.

RicoZhou_0-1664433697179.png

 

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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors