sumproduct

[Guest]

I would like to create a formula that can take the time entered into rows
"a2:a5", and convert it into one of three shifts (A, B, or C) in column b.
The times for the three shifts are: "A"= 0701-1500, "B"=1501-2300, and
"C"=2301-0700.
A B
1 Time Shift
2 07:35 A
3 16:45 B
4 03:30 C
5 10:30 A

I've tried sumprodu

 

[daddylonglegs]

One way would be

=IF(A2>"23:01"+0,"C",IF(A2>"15:01"+0,"B",IF(A2>"07:01"+0,"A","C")))

or possibly

=LOOKUP((A2-"00:01")*24,{0,7,15,23;"C","A","B","C"})

 

[JE McGimpsey]

One way:

=CHOOSE(MOD(A2+59/1440,1)*3+1,"C","A","B")

 

[Guest]

Assuming these are really numbers entered as times, you could just use an IF
statement: In c2: =if(b2<=7/24,"C",if(b2<=15/24,"A",if(b2<=23/24,"B","C"))).
Copy the formula down through row 5.
(Division by 24 is to adjust for Excel's treatment of dates and times. One
day has a numeric value of 1, so an hour is 1/24, etc).

 

[Guest]

Thank you very much for your quick response. In order to get the :01 times to
roll over, I had to add "=" to the > in the if statement, then it worked. The
Lookup worked great for every sample I entered. I really appreciate your help.

 

[Guest]

Thanks bpeltzer for your quick response. Every sample I plugged in worked
very well. I appreciate your help.

 

[Guest]

JE, I really appreciated your quick response. All of the sample times I
entered worked very well, with the exception of 23:01. I received the
"#value" error. It worked fine for 23:00 and 23:02.