AND or NESTED IF FUNCTION

[triton]

I'm attempting to write a formula that looks at a specific time and gives a numeric value for each of the different time frames it may fall in between.

Time Stamp = 5:58:00

Start End Outcome looking for
7:00 AM 5:00 PM 1
5:00 PM 7:00 AM 2
7:00 AM 7:00 PM 3
7:00 PM 7:00 AM 4

Hopefully I'm some sense to this.

 

[Jonmo1]

Those time frames overlap, so your given example 5:58:00 (that's AM right)
Is that 2 or 4 ?

 

[triton]

I see part of problem. Maybe a little more insight will help. I'm trying to take a specific time during the day and calculate what production shift that time applies to. So the 7a-5p is shift 1, 5p-7a is shift 2, 7a-7p is shift 3 and 7p-7a is shift 4. On top of that shifts 1 and 2 are M-Th and shifts 3 and 4 are Fri-Su. My thought was to apply a numeric value to each shift time period and then based on that value and the day of the week I could then use an IF statement to determine what shift it is. I'm having problems with applying a value to the time frames based off of a specific time. And yes 5:58:00 is AM. Hope this makes more sense