Forum Discussion

Copper Contributor

Nov 07, 2022

Solved

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,...

Formulas and Functions

HansVogelaar
Nov 07, 2022
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")))

Bronze Contributor

Nov 07, 2022

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"))

Copper Contributor

Nov 07, 2022

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"

HansVogelaar
MVP
Nov 07, 2022
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")))
- Showtime11214
  Copper Contributor
  Nov 07, 2022
  This worked, thank you so much!
  - JoeUser2004
    Bronze Contributor
    Nov 07, 2022
    HansVogelaar 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") .

Resources