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:

[TABLE="width: 335"]
<tbody>[TR]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD][/TD]
[TD]Range[/TD]
[/TR]
[TR]
[TD="align: right"]9:01[/TD]
[TD="align: right"]11:00[/TD]
[TD][/TD]
[TD]9:01 AM - 11 AM[/TD]
[/TR]
[TR]
[TD="align: right"]11:01[/TD]
[TD="align: right"]13:00[/TD]
[TD][/TD]
[TD]11:01 AM - 1 PM[/TD]
[/TR]
[TR]
[TD="align: right"]13:01[/TD]
[TD="align: right"]15:00[/TD]
[TD][/TD]
[TD]1:01 PM - 3 PM[/TD]
[/TR]
[TR]
[TD="align: right"]15:01[/TD]
[TD="align: right"]17:00[/TD]
[TD][/TD]
[TD]3:01 PM - 5 PM[/TD]
[/TR]
[TR]
[TD="align: right"]17:01[/TD]
[TD="align: right"]19:00[/TD]
[TD][/TD]
[TD]5:01 PM - 7 PM[/TD]
[/TR]
[TR]
[TD="align: right"]19:01[/TD]
[TD="align: right"]21:00[/TD]
[TD][/TD]
[TD]7:01 PM - 9 PM[/TD]
[/TR]
[TR]
[TD="align: right"]21:01[/TD]
[TD="align: right"]9:00[/TD]
[TD][/TD]
[TD]9:01 PM - 9 AM[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 220"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Call Received Hour Range[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD="align: right"]12:30 AM[/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD="align: right"]8:00 AM[/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD="align: right"]12:00 PM[/TD]
[TD]No Time Recorded[/TD]
[/TR]
[TR]
[TD="align: right"]12:00 PM[/TD]
[TD]No Time Recorded[/TD]
[/TR]
</tbody>[/TABLE]

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!