Create shift column depending on the time

[pagliaci]

Could someone help, please? I am creating a new column that I want to display shift "1" "2" or "3" depending on the time of my production order. I tried to use

Turno =

/* Shift 1 6:20:00 - 14:20:00*/

/* Shift 2 14:20:01 - 22:20:00*/

/* Shift 3 22:20:01 - 06:19:59*/

IF (

HOUR ( [Hora PO] ) >= 6

&& HOUR ( [Hora PO] ) <= 14,

1,

IF (

HOUR ( [Hora PO] ) > 14

&& HOUR ( [Hora PO] ) <= 22,

2, 3))

But 2 things are wrong: My column "Turno" has always the value 3 and I can´t compare the minutes (in red), just full hours. I need to have the exactly shift times: 

Shift 1 6:20:00 - 14:20:00*/

/* Shift 2 14:20:01 - 22:20:00*/

/* Shift 3 22:20:01 - 06:19:59*/

Thank you so much!!!

[pagliaci]

Thank you so much!

[VahidDM · Accepted solution]

oops, forgot to add return in the code

try this

Measure =
Var _A= time(6,20,0)
Var _B = time(14,20,00)
Var _C = time(22,20,00)
Var _D = time(06,19,59)
return
IF (
HOUR ( [Hora PO] ) >= _A
&& HOUR ( [Hora PO] ) <= _B,

1,
IF (
HOUR ( [Hora PO] ) > _B
&& HOUR ( [Hora PO] ) <= _C,
2, 3))

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[pagliaci]

It keeps getting the same error, the last column is always returning 3, even though it is morning time (1st column)..

[VahidDM]

@pagliaci 

Sorry I forgot to remove HOUR in the code, please try this to add your column:

VAR _A =
    TIME ( 6, 20, 0 )
VAR _B =
    TIME ( 14, 20, 00 )
VAR _C =
    TIME ( 22, 20, 00 )
VAR _D =
    TIME ( 06, 19, 59 )
RETURN
    IF (
        [Hora PO] >= _A
            && [Hora PO] <= _B,
        1,
        IF ( [Hora PO] > _B && [Hora PO] <= _C, 2, 3 )
    )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!

[VahidDM]

Hi @pagliaci 

Use variables:

Measure =
Var _A= time(6,20,0)
Var _B = time(14,20,00)
Var _C = time(22,20,00)
Var _D = time(06,19,59)
IF (
HOUR ( [Hora PO] ) >= _A
&& HOUR ( [Hora PO] ) <= _B,

1,
IF (
HOUR ( [Hora PO] ) > _B
&& HOUR ( [Hora PO] ) <= _C,
2, 3))

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!