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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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