Need help splitting up times in excel into 3 shifts

[Showtime11214]

Need help splitting up times in excel into 3 shifts which are 7:01 to 15:00, 15:01 to 23:00, and 23:01 to 7:00. I am trying to use this formula and I don't know why it's not working- =IF(AND(TEXT(A2,"hh:mm")>="07:00",TEXT(A2,"hh:mm")<"15:00"),"Shift 1",IF(AND(TEXT(A2,"hh:mm",>="15:00",TEXT(A2,"hh:mm")<"23:00"),"Shift 2","Shift 3")).

Please help thank you 

[#2]

@Showtime11214 TEXT() function will return output as text string. So, you can't compare them with other value like numerical expression. Rather compare with true time value which will give you accurate result. Try below formula-

=IF(AND(A2>=TIME(7,1,0),A2<=TIME(15,0,0)),"SHIFT-1",IF(AND(A2>=TIME(15,1,0),A2<=TIME(23,0,0)),"SHIFT-2","SHIFT-3"))

[#3]

Thanks for your help but all the shift values returned as shift 3. Also, it is worth it to note that the format of my time is "4/11/2022 8:39"

[#4 · Accepted solution]

@Showtime11214 

Try this version:

=IF(MOD(A2,1)<=TIME(7,0,0),"Shift 3",IF(MOD(A2,1)<=TIME(15,0,0),"Shift 1",IF(MOD(A2,1)<=TIME(23,0,0),"Shift 2","Shift 3")))

[#5]

This worked, thank you so much!

[#6]

@Hans Vogelaar  wrote:

=IF(MOD(A2,1)<=TIME(7,0,0),"Shift 3",IF(MOD(A2,1)<=TIME(15,0,0),"Shift 1",
IF(MOD(A2,1)<=TIME(23,0,0),"Shift 2","Shift 3")))

But beware of binary arithmetic anomalies.  Consider the following:

I think you expect "Shift2" in B4, since you originally wrote ``3 shifts which are 7:01 to 15:00, 15:01 to 23:00, and 23:01 to 7:00

IMHO, it is more reliable to write
=IF(--TEXT(A2,"h:m")<=TIME(7,0,0),"Shift 3",
IF(--TEXT(A2,"h:m")<=TIME(15,0,0),"Shift 1",
IF(--TEXT(A2,"h:m")<=TIME(23,0,0),"Shift 2", "Shift 3")))

If you want to use the newer IFS function:

=IFS(--TEXT(A2,"h:m")<=TIME(7,0,0),"Shift 3",
--TEXT(A2,"h:m")<=TIME(15,0,0),"Shift 1",
--TEXT(A2,"h:m")<=TIME(23,0,0),"Shift 2", TRUE,"Shift 3")

The double negation ("--") converts text to a numeric result.

If that is too subtle, use 1*TEXT(A2, "h:m") .