Forum Discussion

PWSNEALE's avatar
PWSNEALE
Frequent Visitor
2 years ago
Solved

Return a value based on time range

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    2 years ago

    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 ( ....)

            )

        )

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual
    2 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.