Split a time entry into shifts

[jfm]

Would anyone have a vba function or worksheet formula that
would do the following:

I have three shifts. The first shift is 3am - 10am, the
second is 10am to 7pm, and the third is 7pm to 3am. I
need to be able assign a time entry to one of the three
shifts. For example, 10:30am would be assigned to shift
2. The function has to allow for the three shifts to
change start and end times but they will never overlap.
Again for example, the shifts might change to 6am to 2pm
(shift 1), 2pm to 10pm (shift 2), and 10pm to 6am (shift
3). After the change 10:30am now is assigned to shift 3.

Any help is appreciated. Thanks.

 

[John]

with this in cells c4:e6

shift 1 shift 2 shift 3
7:00 AM 3:00 PM 11:00 PM
2:59 PM 10:59 PM6:59 AM

and a start time in cell a8

=IF(A8<$E$5,IF(A8<$D$5,IF(A8>=$C$5,$C$4,$E$4),$D$4))

seems to give the result desired

Note cells are formatted as time 1:30 PM

and you enter 10:00 am as 10:00 and 2:30 PM as 14:30

John