Which shift the date/time falls [for lookup]

[Grizlore]

I have a lots of cells which contain date and times (eg 11/11/2010 14:39) these refer to a production date on a particular shift.

The shift pattern are quite complicated and illogical to calculate, however the each shift starts at either 07:00, 15:00 or 23:00

Therefore, I was thinking the easiest way to lookup which shift this date and time refers, is to do a do a formula to see which shift the time falls into.

Then. once I know which shift, I can do a vlookup which looks at a table with four columns

Date – 0700 – 1500 – 2300

Which shift is working when can be added into this table.

The vlookup will look at column 2 if a morning shift (0700), column 3 if afternoons (1500)etc…

I guess this the best way to go about this?

Question.... Which formula function would be best to look at the date and time, 11/11/2010 14:39, and calculate which of the three shift the time falls?

thanks for your time

Any help and pointer would be much appreciated

 

[DonkeyOte]

Based on your post:

=LOOKUP(MOD(A1,1)*24,{0,7,15,23},{3,1,2,3})
where A1 holds DateTime

the number returned would indicate the column to use for ascertaining specific shift (1 being morning etc...)

 

[Grizlore]

Many thanks, that returns just what I need (1, 2 or 3)

with your help, I have created the formula I need, as stated below

=VLOOKUP(WEEKNUM(A2),Shifts!A:D,LOOKUP(MOD(A2,1)*24,{0,7,15,23},{4,2,3,4}),FALSE)

much appreciated