Forum Discussion
Need help splitting up times in excel into 3 shifts
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
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 Replies
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"))- 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"
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")))
