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 Su...
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.

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!