# Finding shift worked based on time

#### phaneufs

##### New Member
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

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

<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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
try

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

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:
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?

are you sure you get #N/A because I am getting Days Shift

Replies
2
Views
416
Replies
0
Views
197
Replies
3
Views
147
Replies
5
Views
250
Replies
9
Views
559

1,217,357
Messages
6,136,085
Members
449,990
Latest member
orthodmd

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

### Which adblocker are you using?

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

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