Forum Discussion
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 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
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 * _DAYDIFFResult is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 * _DAYDIFFResult is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.