DAX Formula - Time

[nataliesmiy1357]

Hello!  I am looking to create a formula that's if this and that, then this with time.

I want:  If the time is between 6am-5:10pm, then "First Shift"

          and  if the time is between 5:25pm - 5:55am, then "Second Shift"

Thanks in advance! 

[rajendraongole1 · Accepted solution]

Hi @nataliesmiy1357 - create below calculated column in your table, replace your Datetime column as per your model.

Shift Classification =

VAR CurrentTime = [DateTime]  

VAR TimeFirstShiftStart = TIME(6, 0, 0)   -- 6:00 AM

VAR TimeFirstShiftEnd = TIME(17, 10, 0)   -- 5:10 PM

VAR TimeSecondShiftStart = TIME(17, 25, 0)  -- 5:25 PM

VAR TimeSecondShiftEnd = TIME(5, 55, 0)  -- 5:55 AM (next day)

RETURN

IF (

    (CurrentTime >= TimeFirstShiftStart && CurrentTime <= TimeFirstShiftEnd) ||

    (CurrentTime >= TimeSecondShiftStart || CurrentTime <= TimeSecondShiftEnd),

    "First Shift",

    "Second Shift"

)

Hope it helps