topic Re: Calculate working time between 2 timestamp in difference day in DAX Commands and Tips

Calculate working time between 2 timestamp in difference day

Anonymous — Wed, 28 Sep 2022 08:23:10 GMT

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

Re: Calculate working time between 2 timestamp in difference day

Anonymous — Thu, 29 Sep 2022 06:41:51 GMT

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.

Re: Calculate working time between 2 timestamp in difference day

Anonymous — Thu, 29 Sep 2022 07:07:18 GMT

Hi Anonymous

It worked totally Perfect!

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