Identify Shift from time stamp

[Diego Sole]

I have a table that identifies the start and end time of each shift throughout the week.

Shift | Start | End |
1 | 1/1/15 7:00AM | 1/1/15 5:00PM |
2 | 1/1/15 5:00PM | 1/2/15 7:00AM |
etc.

I also have a table that contains time stamped job information. I would like a simple formula (I can write a giant if then formula to work, but there should be a more elegant solution) that will return the shift number in which the work was done.

 

[Hui]

Diego

Firstly, Welcome to the Chandoo.org Forums

Can you post a sample file ?

 

[Diego Sole]

Please see attached sample file.

 

[NARAYANK991]

Hi ,

Your table has a lot of overlap ; how do you resolve this ?

A date + time can fall in shift 2 or shift 3 ; similarly , a date + time can fall in shift 3 , shift 4 or shift 5.

Narayan