Question: I have a column of time data formatted like this: `hh:mm:ss`

I want to make an IF statement that returns 1, 2 or 3 corresponding to first, second or third shift if the times fall between certain values.

```
=IF(AND(E50>8,0,0,E50<16,0,0),"First",(IF(AND(E50>=16,0,0,E50<24,0,0), "Second", "Third")))

```

That's what it looks like now, but it doesn't work.

Answer: 
As a short formula:

```
=CHOOSE(INT(1+MOD(E50,1)*3),"Third","First","Second")

```

will do what you want.

If the time is just time (no date) then

```
=CHOOSE(INT(1+E50*3),"Third","First","Second")

```

also will do.

Also this formulas should be very fast ;)

**EDIT**

To make it completely "variable" you would need something like this (timetable is at I51:J53 ascendenting order):

```
=INDEX($J$51:$J$53,COUNTIF($I$51:$I$53,"<="&MOD(E50,1))+IF(COUNTIF($I$51:$I$53,">"&MOD(E50,1))=3,3))

```

It would look like this:

[![enter image description here](https://i.stack.imgur.com/XlpPy.png)](https://i.stack.imgur.com/XlpPy.png)

Note: If you split up the shift which goes from prior 0:00 to past 0:00 so the table would look like:

```
12:00 AM | Third
06:01 AM | First
12:22 PM | Second
10:54 PM | Third

```

then you can skip the formula to:

```
=INDEX($J$51:$J$54,COUNTIF($I$51:$I$54,"<="&MOD(E50,1)))

```

because `+IF(COUNTIF($I$51:$I$53,">"&MOD(E50,1))=3,3)` is only for the case nothing is found (which would be the last shift)...
I found this to be the simplest way to convert time into 3 shifts. The plus 1 allows for excel returning midnight to 00:59 as 0. (so in the formula below, the first six results of the choose function are `"C"` referring to night shift, then at 6am the shift changes to `"A"` day shift. and so on. `"R3"` is the cell i had the time in.

```
=CHOOSE(HOUR(R3)+1, "C", "C", "C", "C","C","C", "A", "A", "A", "A", "A", "A", "A","A","B","B","B","B","B","B","B","B","C","C")

```