Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Hi @v-rzhou-msft
It worked totally Perfect!
I'm really appreciated and Thank you very much your help Master.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |