Using the IF and SEARCH function to determine Shift Type based on Schedule Time

[cmanlongat]

[TABLE="width: 1024"]
<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Shift Clock In Times/Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]Schedule[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Day = 5a - 1P[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Shift[/TD]
[TD][/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Swing = 2P -11P[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Swing[/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]2P - 10P[/TD]
[TD]2P - 10P[/TD]
[TD]2P - 10P[/TD]
[TD]2P - 10P[/TD]
[TD]2P - 10P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Grave = 1A - 4A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grave[/TD]
[TD][/TD]
[TD]1A - 9A[/TD]
[TD]1A - 9A[/TD]
[TD]1A - 9A[/TD]
[TD]1A - 9A[/TD]
[TD]1A - 9A[/TD]
[TD]OFF [/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD][/TD]
[TD]10A - 6P[/TD]
[TD]10A - 6P[/TD]
[TD]10A - 6P[/TD]
[TD]OFF [/TD]
[TD]OFF [/TD]
[TD]10A - 6P[/TD]
[TD]10A - 6P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Hi All,

I have a spreadsheet just like this where Shift is in A4 and Monday is in C4 as well.

I need a formula that will give me either "Day", "Swing", or "Grave" under Shift based on the employees schedule on the right side. Is there any way that this could be accomplished? I tried searching for specific text in a cell, but it is skewed whenever "OFF" is stated. Also, since there are SO many different shift times, I would individually have to search a new schedule time in a new IF formula -- it's messy.

If any of you could provide insight on how to do this, I'd appreciat it immensley. Thank you all!

 

[cmanlongat]

Any help please?

 

[dUBBINS]

=IF($B$2="OFF","",A2)

Etc. But I am wondering exactly what you want here. Because in your example it would say all 3. Unless you have a whole other calendar that this is only reporting those 3 replies. Show what result you are looking to get.

 

[cmanlongat]

Thanks for your reply. I apologize, let me break it down for you and tell you what result I am searching for:

Imagine the entire A column under shift being blank all the way down. On Column C (schedules), those are all in excel in the same format above which goes down to about 120 cells.

What I want:

I want a formula that I can input in A5 (cell under Shift) that will give me the employees type of shift (Day, Swing, or Grave) based on the schedule right next to cell A5.

So initially I tried a basic Text in a Specific cell formula where I used IF(ISNUMBER(SEARCH("2P - 10P",C5), "Swing", " ") -- However this is not useful at all since I would have to perform this function for EVERY single shift time, and also that if an employee has the words "OFF" on the same column, it would leave me a blank, where that employee could potentially have a Swing shift later on in the week.

 

[Tetra201]

I still do not understand your layout, but here is a formula that returns shift type based on the shift clock-in time:

=IFERROR(CHOOSE(ROUND(REPLACE(TRIM(LEFT(SUBSTITUTE(C5,"-"," "),3)),LEN(TRIM(LEFT(SUBSTITUTE(C5,"-"," "),3))),0,":00 ")*24/9.3,0)+1,"Grave","Day","Swing"),"Off")

 

[cmanlongat]

I apologize - the layout is an example of what I want to be done. Just imagine cell A being completely blank and column C4:I4 being filled with schedules.

I will try this formula in a few minutes - but yes! I did want it to return either day shift or grave based on the shift times and example shown

 

[cmanlongat]

Do you mind telling me what the formula means?

 

[cmanlongat]

This is a working formula however for shift times such as "5:30P - 1:30A" or any time with :30 in it, it is not giving me a result, it says "OFF" instead. I notice that this happens for all times that have :30 in it.
Any help?

 

[cmanlongat]

Someone please help in explaining this formula (what does it mean?) user Tetra201 provided so I can tweak it up a little bit. Thanks in advance.

 

[James006]

Hello,

To be on the safe side, you could include a tweak for the half hour ...

=IFERROR(CHOOSE(ROUND(REPLACE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(C5,":30",""),"-"," "),3)),LEN(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(C5,":30",""),"-"," "),3))),0,":00 ")*24/9.3,0)+1,"Grave","Day","Swing"),"Off")

HTH