Chaining Multiple IF Statements in Excel to Calculate Work Shifts from Times -

i trying calculate work shift based on time entered in excel worksheet. shift allocation below:

if time >= 05:30 pm & <= 01:30 - 1st shift

if time >= 01:30 & <= 09:30 - 2nd shift

if time >= 09:30 & <= 01:30 - 3rd shift

i using if , conditions still it's showing incorrect results. below formula using.

=if(and(d2>=timevalue("05:30:00 pm"),(d2<=timevalue("01:30:00 am"))),"1",  if(and(d2>=timevalue("01:30:00 am"),(d2<=timevalue("09:30:00 am"))),"2","3")) 

any idea doing wrong?

the first problem in shift definitions. time 1:30 am falls in 3 shifts due use of less-than-or-equal-to operators. these should changed less-than operators, such following:

if time >= 5:30 pm & < 1:30 - 1st shift

if time >= 1:30 & < 9:30 - 2nd shift

if time >= 9:30 & < 5:30 pm - 3rd shift (note: corrected apparent typo here well)

the second problem in first if statement:

if(and(d2>=timevalue("05:30:00 pm"),(d2<=timevalue("01:30:00 am"))),"1",...) 

the time can't simultaneously both greater 5:30:00 pm , less 01:30:00 am. times in excel stored numbers. timevalue("1:30 am") returns 0.06250 , timevalue("5:30 pm") returns approximately 0.72917. result if statement translates following:

if(and(d2>=0.72917, d2<=0.06250), "1", ...) 

d2 cannot both greater-than-or-equal-to 0.72917 , less-than-or-equal-to 0.06250. result return false.

try rewriting code follows:

=if(d2<timevalue("1:30 am"),"1",     if(d2<timevalue("9:30 am"),"2",        if(d2<timevalue("5:30 pm"),"3","1")))