Formula to calculate on what work shift that event occurred

Good Evening All,
I could use a spot of help calculating which work shift an event occurred on with a workbook that has 1M or more records. Basic these are traffic counts or events.
The imported data has the date and time of the event in this format 01/25/2019 5:46:53 PM UTC-05:00, i have extracted the time into its own column using =MID(A4,10,11) So I have the time of the event, and need to determine what shift the event occurred on Shift One 10:00pm-6:00am, Shift Two 6am-2pm, Shift 3 2pm-10pm

I'm a little unsure of how to proceed here. I'm supposing an IF command for the times of each shift and then equals the name of the shift and I'm search for a solution. Will the passing of midnight break the formulas. The eventual is a count of of the number of records that occurred on a particular shift, and then present them in a table

Any Help would be appreciated

Jim Manning

I don't think that MID function will give you correct times - it will return a text value that looks like a time.

If you treat the times of the day like this:

Midnight ….. Shift One
6:00 am …. Shift Two
2:00 pm …. Shift Three
10:00pm …. Shift One

the you can use that tabular layout to determine the shift from the time using a VLOOKUP formula.

Hope this helps.

Pete