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

excel

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

Showtime11214

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