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

Friday	0:00	Night Shift
	6:00	Day Shift
	13:00	Back Shift
Saturday	0:00	OT
Sunday	0:00	OT
	23:00	Night Shift
Weekday	0:00	Night Shift
	6:00	Day Shift
	14:00	Back Shift
	22:00	Night Shift

<tbody>
</tbody>

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!