Formula to put specific hour within a set time range

[eckecko]

Good afternoon. I hope everyone is having an awesome day. I would like to ask a question please.

I have a sheet that shows the time customers call. I need to put different hours into a specific time range. Thus I have set up the following table:

Start Time	End Time		Range
9:01	11:00		9:01 AM - 11 AM
11:01	13:00		11:01 AM - 1 PM
13:01	15:00		1:01 PM - 3 PM
15:01	17:00		3:01 PM - 5 PM
17:01	19:00		5:01 PM - 7 PM
19:01	21:00		7:01 PM - 9 PM
21:01	9:00		9:01 PM - 9 AM

<tbody>
</tbody>

I am using the following formula:

=IF(AND(J2>=' Reference Tables'!$A$2,'Raw Data'!J2<=' Reference Tables'!$B$2),' Reference Tables'!$C$2,
IF(AND(J2>=' Reference Tables'!$A$3,'Raw Data'!J2<=' Reference Tables'!$B$3),' Reference Tables'!$C$3,
IF(AND(J2>=' Reference Tables'!$A$4,'Raw Data'!J2<=' Reference Tables'!$B$4),' Reference Tables'!$C$4,
IF(AND(J2>=' Reference Tables'!$A$5,'Raw Data'!J2<=' Reference Tables'!$B$5),' Reference Tables'!$C$5,
IF(AND(J2>=' Reference Tables'!$A$6,'Raw Data'!J2<=' Reference Tables'!$B$6),' Reference Tables'!$C$6,
IF(AND(J2>=' Reference Tables'!$A$7,'Raw Data'!J2<=' Reference Tables'!$B$7),' Reference Tables'!$C$7,
IF(AND(J2>=' Reference Tables'!$A$8,'Raw Data'!J2<=' Reference Tables'!$B$8),' Reference Tables'!$C$8,"No Time Recorded")))))))

A call received at 8AM for example should be within the 9PM and 9AM time range. People that call at 12PM should be in the 11AM-13PM but I get the message "No time recorded." Can someone please tell me what I am doing wrong? Thank you!

Below is a sample of the erroneous results:

Time	Call Received Hour Range
-	No Time Recorded
-	No Time Recorded
-	No Time Recorded
-	No Time Recorded
12:30 AM	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
	No Time Recorded
8:00 AM	No Time Recorded
12:00 PM	No Time Recorded
12:00 PM	No Time Recorded

<tbody>
</tbody>

Thank you for your time.

 

[Andrew Poulsom]

Welcome to MrExcel.

Use a lookup table:

Excel VLookup Function Examples

It must be in ascending order so insert a row at the beginning to cover 00:01 to 09:00.

For your formula to work you would need to add a day to times before 09:01 and also to the 09:00 in B8.

 

[eckecko]

It worked! Thank you so much. I am learning to use VLOOKUP. I also used the =IFERROR for those cells that were blank. Thanks again. Have a wonderful day!