help with formula to return text based on time values falling within range

[BSWJON]

Hi All,

I have a data file with date values in column B and time values in column J. I would like to insert a formula into column O that returns the shift which the time value pertains to. The shifts are as follows:

0600-1400: Day Shift
14:00 to 22:00: Back Shift
22:00 to 06:00: Night shift.
Fridays
0600 to 13:00 Day Shift
13:00 to 20:00 Back Shift
Sunday
23:00-0600: Night Shift
All other weekend times: OT.

Probably best to ignore the variances for fridays and weekends at the moment, unless it is easier to encorporate than I imagine?

In any case, it would be great to have some help!

Jon

 

[BSWJON]

I should add that I have tried =IF(AND( formulas as well as =INDEX but neither have worked so far.

Help!

 

[palaeontology]

I don't pretend to understand your time-frames and shift-naming system, so I might have this a bit wrong, and assuming you have a single time in each cell in column J (as opposed to a time range), then the following might work ...

in column O you could use ... =IF((TEXT(B2,"dddd"))="Friday",VLOOKUP(J2,$S$2:$T$4,2),IF((TEXT(B2,"dddd"))="Saturday",VLOOKUP(J2,$S$5:$T$5,2),IF((TEXT(B2,"dddd"))="Sunday",VLOOKUP(J2,$S$6:$T$8,2),VLOOKUP(J2,$S$9:$T$11,2))))

you'd need the following table in the range R2:T11 ...
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Friday[/TD]
[TD]0:00[/TD]
[TD]Night Shift[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6:00[/TD]
[TD]Day Shift[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]13:00[/TD]
[TD]Back Shift[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]0:00[/TD]
[TD]OT[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]0:00[/TD]
[TD]OT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]23:00[/TD]
[TD]Night Shift[/TD]
[/TR]
[TR]
[TD]Weekday[/TD]
[TD]0:00[/TD]
[TD]Night Shift[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6:00[/TD]
[TD]Day Shift[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]14:00[/TD]
[TD]Back Shift[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]22:00[/TD]
[TD]Night Shift[/TD]
[/TR]
</tbody>[/TABLE]


Even if there are errors in my understanding of your 'shift' system, you should be able to amend the table accordingly.

Kind regards,

Chris

 

[BSWJON]

To my amazement- this worked perfectly, first time. Thank you very much!