Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the below table:
MACHINE | LOADTIME | UNLOADTIME | Process Time (Unload-Load) |
M01 | 2022/08/04 6:32:00 | 2022/08/04 14:42:00 | 490 |
M07 | 2022/08/04 6:15:00 | 2022/08/04 13:57:00 | 462 |
M03 | 2022/08/04 3:24:00 | 2022/08/04 11:35:00 | 491 |
M06 | 2022/08/04 2:02:00 | 2022/08/04 9:45:00 | 463 |
M02 | 2022/08/04 1:02:00 | 2022/08/04 9:28:00 | 506 |
M05 | 2022/08/04 0:20:00 | 2022/08/04 7:58:00 | 458 |
M04 | 2022/08/03 21:54:00 | 2022/08/04 6:13:00 | 499 |
M01 | 2022/08/03 20:51:00 | 2022/08/04 5:18:00 | 507 |
M08 | 2022/08/03 20:33:00 | 2022/08/04 4:15:00 | 462 |
M07 | 2022/08/03 19:20:00 | 2022/08/04 3:25:00 | 485 |
M06 | 2022/08/03 15:35:00 | 2022/08/03 23:22:00 | 467 |
M05 | 2022/08/03 15:04:00 | 2022/08/03 22:46:00 | 462 |
M03 | 2022/08/03 14:31:00 | 2022/08/03 22:36:00 | 485 |
M08 | 2022/08/03 12:07:00 | 2022/08/03 19:51:00 | 464 |
M07 | 2022/08/03 10:58:00 | 2022/08/03 18:41:00 | 463 |
M04 | 2022/08/03 10:02:00 | 2022/08/03 18:15:00 | 493 |
M02 | 2022/08/03 7:44:00 | 2022/08/03 15:58:00 | 494 |
M05 | 2022/08/03 5:59:00 | 2022/08/03 13:41:00 | 462 |
M06 | 2022/08/03 5:24:00 | 2022/08/03 13:09:00 | 465 |
M03 | 2022/08/03 4:27:00 | 2022/08/03 12:34:00 | 487 |
M01 | 2022/08/03 2:54:00 | 2022/08/03 11:10:00 | 496 |
M08 | 2022/08/03 1:06:00 | 2022/08/03 8:50:00 | 464 |
Process = UNLOAD-LOAD*1440
We are a 24/7 operation...M shift = 7am-7pm and E shift =7pm-7am. How can I assign the correct number of minutes to each shift? Specifically, production that crosses midnight has me baffled.
Any help is greatly appreciated!
Solved! Go to Solution.
Hi @robandemmy ,
I forgot to modifiy something in the E shift calculation, it has been changed now. Please try:
M shift =
VAR _a =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _b =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 19, 0, 0 )
VAR _c =
DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _sameday =
SWITCH (
TRUE (),
[LOADTIME] < _a
&& [UNLOADTIME] < _b, DATEDIFF ( _a, [UNLOADTIME], MINUTE ),
[LOADTIME] < _a
&& [UNLOADTIME] >= _b, DATEDIFF ( _a, _b, MINUTE ),
[LOADTIME] >= _a
&& [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
[LOADTIME] >= _a
&& [LOADTIME] <= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _b, MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _b, 0
)
VAR _diffday =
SWITCH (
TRUE (),
[LOADTIME] < _b
&& [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], _b, MINUTE ),
[LOADTIME] < _b
&& [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _b, MINUTE ) + DATEDIFF ( _c, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] < _c, 0,
[LOADTIME] >= _b
&& [UNLOADTIME] >= _c, DATEDIFF ( _c, [UNLOADTIME], MINUTE )
)
RETURN
IF ( _a = _c, _sameday, _diffday )
E shift =
VAR _a =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _b =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 19, 0, 0 )
VAR _c =
DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _sameday =
SWITCH (
TRUE (),
[LOADTIME] < _a
&& [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], _a, MINUTE ),
[LOADTIME] < _a
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _a, MINUTE ) + DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _a
&& [UNLOADTIME] < _b, 0,
[LOADTIME] >= _a
&& [LOADTIME] <= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE )
)
VAR _diffday =
SWITCH (
TRUE (),
[LOADTIME] < _b
&& [UNLOADTIME] < _c, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] < _b
&& [UNLOADTIME] >= _c, DATEDIFF ( _b, _c, MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _c, MINUTE )
)
RETURN
IF ( _a = _c, _sameday, _diffday )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @robandemmy ,
I forgot to modifiy something in the E shift calculation, it has been changed now. Please try:
M shift =
VAR _a =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _b =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 19, 0, 0 )
VAR _c =
DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _sameday =
SWITCH (
TRUE (),
[LOADTIME] < _a
&& [UNLOADTIME] < _b, DATEDIFF ( _a, [UNLOADTIME], MINUTE ),
[LOADTIME] < _a
&& [UNLOADTIME] >= _b, DATEDIFF ( _a, _b, MINUTE ),
[LOADTIME] >= _a
&& [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
[LOADTIME] >= _a
&& [LOADTIME] <= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _b, MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _b, 0
)
VAR _diffday =
SWITCH (
TRUE (),
[LOADTIME] < _b
&& [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], _b, MINUTE ),
[LOADTIME] < _b
&& [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _b, MINUTE ) + DATEDIFF ( _c, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] < _c, 0,
[LOADTIME] >= _b
&& [UNLOADTIME] >= _c, DATEDIFF ( _c, [UNLOADTIME], MINUTE )
)
RETURN
IF ( _a = _c, _sameday, _diffday )
E shift =
VAR _a =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _b =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 19, 0, 0 )
VAR _c =
DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _sameday =
SWITCH (
TRUE (),
[LOADTIME] < _a
&& [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], _a, MINUTE ),
[LOADTIME] < _a
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _a, MINUTE ) + DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _a
&& [UNLOADTIME] < _b, 0,
[LOADTIME] >= _a
&& [LOADTIME] <= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE )
)
VAR _diffday =
SWITCH (
TRUE (),
[LOADTIME] < _b
&& [UNLOADTIME] < _c, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] < _b
&& [UNLOADTIME] >= _c, DATEDIFF ( _b, _c, MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _c, MINUTE )
)
RETURN
IF ( _a = _c, _sameday, _diffday )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Works great, thanks!!
Hi @robandemmy ,
Please try:
M shift =
VAR _a =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _b =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 19, 0, 0 )
VAR _c =
DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _sameday =
SWITCH (
TRUE (),
[LOADTIME] < _a
&& [UNLOADTIME] < _b, DATEDIFF ( _a, [UNLOADTIME], MINUTE ),
[LOADTIME] < _a
&& [UNLOADTIME] >= _b, DATEDIFF ( _a, _b, MINUTE ),
[LOADTIME] >= _a
&& [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
[LOADTIME] >= _a
&& [LOADTIME] <= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _b, MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _b, 0
)
VAR _diffday =
SWITCH (
TRUE (),
[LOADTIME] < _b
&& [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], _b, MINUTE ),
[LOADTIME] < _b
&& [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _b, MINUTE ) + DATEDIFF ( _c, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] < _c, 0,
[LOADTIME] >= _b
&& [UNLOADTIME] >= _c, DATEDIFF ( _c, [UNLOADTIME], MINUTE )
)
RETURN
IF ( _a = _c, _sameday, _diffday )
E shift =
VAR _a =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _b =
DATE ( YEAR ( [LOADTIME] ), MONTH ( [LOADTIME] ), DAY ( [LOADTIME] ) )
+ TIME ( 19, 0, 0 )
VAR _c =
DATE ( YEAR ( [UNLOADTIME] ), MONTH ( [UNLOADTIME] ), DAY ( [UNLOADTIME] ) )
+ TIME ( 7, 0, 0 )
VAR _sameday =
SWITCH (
TRUE (),
[LOADTIME] < _a
&& [UNLOADTIME] < _b, DATEDIFF ( [LOADTIME], _a, MINUTE ),
[LOADTIME] < _a
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], _a, MINUTE ) + DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _a
&& [UNLOADTIME] < _b, 0,
[LOADTIME] >= _a
&& [LOADTIME] <= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _b, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE )
)
VAR _diffday =
SWITCH (
TRUE (),
[LOADTIME] < _b
&& [UNLOADTIME] < _c, DATEDIFF ( _b, [UNLOADTIME], MINUTE ),
[LOADTIME] < _b
&& [UNLOADTIME] >= _c, DATEDIFF ( _b, _c, MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] < _c, DATEDIFF ( [LOADTIME], [UNLOADTIME], MINUTE ),
[LOADTIME] >= _b
&& [UNLOADTIME] >= _c, DATEDIFF ( [LOADTIME], _c, MINUTE )
)
RETURN
IF ( _a = _c, _sameday, _diffday )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks so much!! It's close but I am getting a few values that aren't coming back correct...looks like units that cross midnight:
I added a total time column as valiodation and am getting negative or incorrect large values when units cross over midnight.
Thanks for the reply...that's a partial solution. What I need to be able to do is assign the minutes to M and E shifts in a 7-7 operation...for example, if a unit loads at 5pm and unloads at 10pm, then M shift gets 120 minutes and E shift gets 180 minutes. That is one of the more simple examples...units that load at 6pm and unload at 2am for instance. M shift would get 60 minutes and E shift 420 minutes. Crossing midnight adds another level of complexity
In Power Query, try this expression in a custom column:
Duration.TotalMinutes([UNLOADTIME] - [LOADTIME])
Proud to be a Super User!
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |