Finding shift worked based on time

phaneufs

New Member
Joined
Mar 31, 2015
Messages
4
Hi there,

What I am looking for is a formula that will tell me what shift was working when a call was placed based on the time of the call. The shifts are as followed:

7:30-15:30 - Days
15:30-23:30- Afternoon
23:30-7:30- On Call

This is what I have came up with so far:

=+IF(C2>="7:30"<"15:30","Day",IF(C2>="15:30","Afternoon","On Call "))&"Shift"

Here is the data and my results from this formula

TimeColumn1
15:02On Call Shift
15:10On Call Shift
17:53AfternoonShift
23:54AfternoonShift
20:34AfternoonShift
09:12On Call Shift
10:26On Call Shift
10:32On Call Shift
10:28On Call Shift
12:20On Call Shift
13:48On Call Shift
09:18On Call Shift
09:59On Call Shift
11:00On Call Shift
15:21On Call Shift
09:26On Call Shift
03:52On Call Shift
01:27On Call Shift
23:18AfternoonShift
10:58On Call Shift
13:16On Call Shift
14:29On Call Shift
19:05AfternoonShift
15:05On Call Shift
16:17AfternoonShift
21:39AfternoonShift

<tbody>
</tbody>


As you can tell if the call was placed in the "Day" shift category it is not yielding the correct answer from this formula. Any help is greatly appreciated

Thanks!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
try

Code:
=LOOKUP(A2,--{"00:00","07:30","15:30","23:30"},{"On Call","Days","Afternoon","On Call"}) & " Shift"
 

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Try VBA Geek's solution.

With you solution, the part
Code:
... C2>="7:30"<"15:30" ...
should have been
Code:
... AND(C2>="7:30",C2<"15:30") ...
 
Last edited:

phaneufs

New Member
Joined
Mar 31, 2015
Messages
4
try

Code:
=LOOKUP(A2,--{"00:00","07:30","15:30","23:30"},{"On Call","Days","Afternoon","On Call"}) & " Shift"


Alright I tried both of the suggestions. Marcel- None of the results changed.

VBA Geek- The formula only works when the call was placed at that given time. i.e: if a call was placed at 7:30 is shows up as days. if the the call was placed at 7:31 then I get the not available error (#N/A). I just need to input the ranges (<,>, or =). What is the proper way to do this?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,675
Messages
5,838,718
Members
430,566
Latest member
ChanchalSingh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top