Return a value based on time range

[PWSNEALE]

Hello, 

I have a table that has a column named SubEventTime and some other irrelevent columns to my question. I need to add a column that will show what shif the event occoured on based on the SubEventTime. I have tried using IF and SWITCH statements, but still cannot get anything to work. 

Time Ranges

6:00 am - 2:30 pm  Return 1st shift

2:30 pm - 12:30 am return 2nd Shift

12:30 am - 6:00 am return 3rd shift

Thanks in advance

Steve

[Anonymous · Accepted solution]

Hi @PWSNEALE ,

Please try this way:

SHIFT = 
VAR CurrentTime = 'Table'[time]
VAR StartTime_1st = TIME(6, 0, 0)
VAR EndTime_1st = TIME(14, 30, 0)
VAR StartTime_2nd = EndTime_1st
VAR EndTime_2nd = TIME(24, 30, 0)
RETURN
    IF(
        AND(CurrentTime >= StartTime_1st, CurrentTime < EndTime_1st),
        "1st shift",
        IF(
            OR(
                AND(CurrentTime >= StartTime_2nd, CurrentTime < EndTime_2nd),
                AND(CurrentTime >= TIME(0, 0, 0), CurrentTime < StartTime_1st)
            ),
            "2nd shift",
            "3rd shift"
        )
    )

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PWSNEALE]

Thank you! this worked well.

[ExcelMonke]

Have you considered using:

Shift = 
SWITCH(
TRUE(),
IF(AND(TIMEVALUE("06:00:00")>TIMEVALUE(Table[Time]) , TIMEVALUE("14:30:00")<Table[Time])),"1st Shift",
IF(AND(TIMEVALUE("14:30:00")>TIMEVALUE(Table[Time]) , TIMEVALUE("00:30:00")<Table[Time])),"2nd Shift",
IF(AND(TIMEVALUE("00:30:00")>TIMEVALUE(Table[Time]) , TIMEVALUE("06:00:00")<Table[Time])),"3rd Shift",
0
)

Do note, you may need to adjust for >= and <= depending on your parameters.

Hope this helps!

[ToddChitt]

Can you post some real data, or at least a screen shot? Also please show your attempts with the DAX IF statement. This should not be too hard:

IF ( [SubEventTime] = "6:00 am - 2:30 pm", "1st shift",

     IF ( ...

         IF ( ....)

        )

    )