if statement for datetime value between two other datetime values

[OSepulvedaIII]

Need help finding formula on where column D (unique value) falls between Column A (static value) and Column B (static value) and return Column C (static value) in Column E.
A B C D E
Start_DateTime End_DateTime SHIFT1 Date_Time
1/1/15 7:00 1/2/15 6:59 C 1/1/15 0:06
1/2/15 7:00 1/3/15 6:59 B 1/1/15 1:01
1/3/15 7:00 1/4/15 6:59 C 1/1/15 1:01
1/4/15 7:00 1/5/15 6:59 A 1/1/15 0:00
1/5/15 7:00 1/6/15 6:59 C 1/1/15 0:02
1/6/15 7:00 1/7/15 6:59 A 1/1/15 0:04
1/7/15 7:00 1/8/15 6:59 B 1/1/15 0:09

[JeteMc]

The following formula is going to produce blanks for all of the cells in column E because all of the date_times in column D are prior to the beginning of the first shift on 1/1/15 at 7:00 a.m. If you put in a date_time that is actually during a shift, that shifts code letter will be returned.

=IF(SUMPRODUCT((A$2:A$8<=D2)*(B$2:B$8>=D2))=0,"",INDEX(C$2:C$8,SUMPRODUCT((A$2:A$8<=D2)*(B$2:B$8>=D2)*(ROW(A$2:A$8)-1))))

Let me know if you have any questions.

[OSepulvedaIII]

JeteMc THANK YOU!! I never even thought of adding that first line of data...It came up blank every time. This works like a charm for my total response time QC project for the Fire Department. Thank you!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.