software

I would like to create a formula that can take the time entered into rows
quot;a2:a5quot;, and convert it into one of three shifts (A, B, or C) in column b.
The times for the three shifts a quot;Aquot;= 0701-1500, quot;Bquot;=1501-2300, and
quot;Cquot;=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


One way would be

=IF(A2gt;quot;23:01quot; 0,quot;Cquot;,IF(A2gt;quot;15:01quot; 0,quot;Bquot;,IF(A2gt;quot;07 :01quot; 0,quot;Aquot;,quot;Cquot;)))

or possibly

=LOOKUP((A2-quot;00:01quot;)*24,{0,7,15,23;quot;Cquot;,quot;Aquot;,quot;Bquot;,quot;Cquot;})--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=519132One way:

=CHOOSE(MOD(A2 59/1440,1)*3 1,quot;Cquot;,quot;Aquot;,quot;Bquot;)

In article gt;,
John997 gt; wrote:

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

Assuming these are really numbers entered as times, you could just use an IF
statement: In c2: =if(b2lt;=7/24,quot;Cquot;,if(b2lt;=15/24,quot;Aquot;,if(b2lt;=23/24,quot;Bquot;,quot;Cquot;))).
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).

quot;John997quot; wrote:

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

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

quot;daddylonglegsquot; wrote:

gt;
gt; One way would be
gt;
gt; =IF(A2gt;quot;23:01quot; 0,quot;Cquot;,IF(A2gt;quot;15:01quot; 0,quot;Bquot;,IF(A2gt;quot;07 :01quot; 0,quot;Aquot;,quot;Cquot;)))
gt;
gt; or possibly
gt;
gt; =LOOKUP((A2-quot;00:01quot;)*24,{0,7,15,23;quot;Cquot;,quot;Aquot;,quot;Bquot;,quot;Cquot;})
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=519132
gt;
gt;

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

quot;bpeltzerquot; wrote:

gt; Assuming these are really numbers entered as times, you could just use an IF
gt; statement: In c2: =if(b2lt;=7/24,quot;Cquot;,if(b2lt;=15/24,quot;Aquot;,if(b2lt;=23/24,quot;Bquot;,quot;Cquot;))).
gt; Copy the formula down through row 5.
gt; (Division by 24 is to adjust for Excel's treatment of dates and times. One
gt; day has a numeric value of 1, so an hour is 1/24, etc).
gt;
gt; quot;John997quot; wrote:
gt;
gt; gt; I would like to create a formula that can take the time entered into rows
gt; gt; quot;a2:a5quot;, and convert it into one of three shifts (A, B, or C) in column b.
gt; gt; The times for the three shifts a quot;Aquot;= 0701-1500, quot;Bquot;=1501-2300, and
gt; gt; quot;Cquot;=2301-0700.
gt; gt; A B
gt; gt; 1 Time Shift
gt; gt; 2 07:35 A
gt; gt; 3 16:45 B
gt; gt; 4 03:30 C
gt; gt; 5 10:30 A
gt; gt;
gt; gt; I've tried sumprodu

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
quot;#valuequot; error. It worked fine for 23:00 and 23:02.

quot;JE McGimpseyquot; wrote:

gt; One way:
gt;
gt; =CHOOSE(MOD(A2 59/1440,1)*3 1,quot;Cquot;,quot;Aquot;,quot;Bquot;)
gt;
gt; In article gt;,
gt; John997 gt; wrote:
gt;
gt; gt; I would like to create a formula that can take the time entered into rows
gt; gt; quot;a2:a5quot;, and convert it into one of three shifts (A, B, or C) in column b.
gt; gt; The times for the three shifts a quot;Aquot;= 0701-1500, quot;Bquot;=1501-2300, and
gt; gt; quot;Cquot;=2301-0700.
gt; gt; A B
gt; gt; 1 Time Shift
gt; gt; 2 07:35 A
gt; gt; 3 16:45 B
gt; gt; 4 03:30 C
gt; gt; 5 10:30 A
gt; gt;
gt; gt; I've tried sumprodu
gt;