IF(AND...

[MD]

I am trying to look at a 24-hour time code (hh:mm) in one cell and return a shift (1st, 2nd, 3rd) in another. The shifts run from 07:00-15:00 (1st), 15:00-23:00 (2nd) and 23:00-07:00 (3rd)

Tried using: IF(AND(B4>=07:00,B4<15:00),"1st",IF(AND(B4>=15:00,B4<23:00),"2nd",IF(AND(B4>=23:00,B4<07:00),"3rd","Time entry error"))

Doesn't seem to like the colons in the function. How do I enter the time? Removing the colons didn't work

Thanks.

 

[Frank Kabel]

Hi
try
IF(AND(B4>=TIME(7,0,0),B4<TIME(15,0,0)),"1st",IF(AND(B4>=TIME(15,0,0),B
4<TIME(23,0,0)),"2nd",IF(AND(B4>=TIME(23,0,0),B4<TIME(7,0,0)),"3rd","Ti
me entry error")))

 

[JE McGimpsey]

One way:

=CHOOSE(MOD((B4+"1:00")*3,3)+1, "3rd","1st","2nd")

 

[MD]

Thanks JE, that works...haven't figured out exactly how yet, but it works.

Frank, tried yours too, but could not get 3rd shift to return (keeps returning "Time entry error"). Couldn't figure it out.

Thnanks!

 

[Frank Kabel]

Hi
just copied your original formula. You may try
IF(AND(B4>=TIME(7,0,0),B4<TIME(15,0,0)),"1st",IF(AND(B4>=TIME(15,0,0),B
4<TIME(23,0,0)),"2nd",IF(OR(B4>=TIME(23,0,0),B4<TIME(7,0,0)),"3rd","Tim
e entry error")))

Though JE's formula is definetly better

 

[David Byrne]

Hi,

Yet another solution is to used Vlookup.

Set up a range as below, named (say) Shifts.

0 3rd
7 1st
15 2nd
23 3rd


Then all you need is = Vlookup(A1,Shifts,2)

Far less typing, hence less chance of an error.

Also more flexible in the event of a change in shift times.

David