sumproduct
 

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

sumproduct
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=519132

sumproduct
 

One way:

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

In article ,
John997 wrote:

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

sumproduct
 

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

"John997" wrote:

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

sumproduct
 

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.

"daddylonglegs" wrote:


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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=519132

sumproduct
 

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

"bpeltzer" wrote:

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

"John997" wrote:

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

sumproduct
 

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.

"JE McGimpsey" wrote:

One way:

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

In article ,
John997 wrote:

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