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:
<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:
<tbody>
</tbody>
Thank you for your time.
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.