Hello, I am trying to write a formula that will tell me if any value in a list of values for a specific user is within a range of values.
For Example I would like to display "Y" in the "Any Projects Time Logged While Working this Request" column of table 1 if any of the values in the "Status End Time" column of table 2 for Availability Status "PROJECTS" fall betwee the range "Time Finished" minus the number of minutes in the "Handle Time in Minutes"
Table 1:
<TBODY>
</TBODY>
Table 2:
<TBODY>
</TBODY>
Any help you can provide is greatly appreciated. I am using Excel 2007. I have highlighted the projects records in table 2 that should result in "Y" records in table 1. Thanks!
For Example I would like to display "Y" in the "Any Projects Time Logged While Working this Request" column of table 1 if any of the values in the "Status End Time" column of table 2 for Availability Status "PROJECTS" fall betwee the range "Time Finished" minus the number of minutes in the "Handle Time in Minutes"
Table 1:
Routing User ID | Time Finished | Handle Time in Minutes | Any Projects Time Logged while working the request? |
BABBS2 | 3/28/2013 10:25:13 AM | 15.166667 | N |
GOARDD1 | 3/28/2013 11:25:17 AM | 10.95 | N |
BABBS2 | 3/28/2013 11:45:19 AM | 61.583333 | N |
DANIEK11 | 3/28/2013 05:07:55 PM | 8.77 | N |
DANIEK11 | 3/28/2013 01:34:13 PM | 5.85 | N |
BABBS2 | 3/28/2013 03:58:19 PM | 24.85 | N |
BABBS2 | 3/28/2013 03:33:18 PM | 138.383333 | Y |
BABBS2 | 3/28/2013 05:31:55 PM | 82.5 | Y |
<TBODY>
</TBODY>
Table 2:
Availability Status</SPAN> | Availability User ID</SPAN> | Status Start Datetime</SPAN> | Status End Datetime</SPAN> |
ADMIN</SPAN> | BABBS2</SPAN> | 03/28/2013 12:11:39 PM</SPAN> | 3/28/2013 01:36:45 PM</SPAN> |
AVAILABLE</SPAN> | BABBS2</SPAN> | 03/28/2013 09:50:29 AM</SPAN> | 3/28/2013 10:08:23 AM</SPAN> |
AVAILABLE</SPAN> | BABBS2</SPAN> | 03/28/2013 10:08:23 AM</SPAN> | 3/28/2013 10:09:47 AM</SPAN> |
PROJECTS</SPAN> | BABBS2</SPAN> | 03/28/2013 02:55:17 PM</SPAN> | 3/28/2013 03:23:15 PM</SPAN> |
PROJECTS</SPAN> | BABBS2</SPAN> | 03/28/2013 04:18:37 PM</SPAN> | 3/28/2013 05:25:35 PM</SPAN> |
PROJECTS</SPAN> | DYSERK</SPAN> | 03/28/2013 09:53:28 AM</SPAN> | 3/28/2013 10:13:12 AM</SPAN> |
AVAILABLE</SPAN> | GOLDENT</SPAN> | 03/28/2013 08:02:41 AM</SPAN> | 3/28/2013 08:36:28 AM</SPAN> |
PROJECTS</SPAN> | GOLDENT</SPAN> | 03/28/2013 10:30:09 AM</SPAN> | 3/28/2013 11:34:46 AM</SPAN> |
AVAILABLE</SPAN> | MCQUOWD2</SPAN> | 03/28/2013 10:27:46 AM</SPAN> | 3/28/2013 10:44:04 AM</SPAN> |
ADMIN</SPAN> | MCQUOWD2</SPAN> | 03/28/2013 10:56:29 AM</SPAN> | 3/28/2013 12:05:27 PM</SPAN> |
AVAILABLE</SPAN> | MCQUOWD2</SPAN> | 03/28/2013 04:48:28 PM</SPAN> | 3/28/2013 05:45:20 PM</SPAN> |
AVAILABLE</SPAN> | WHALENJ</SPAN> | 03/28/2013 02:34:21 PM</SPAN> | 3/28/2013 02:51:31 PM</SPAN> |
<TBODY>
</TBODY>
Any help you can provide is greatly appreciated. I am using Excel 2007. I have highlighted the projects records in table 2 that should result in "Y" records in table 1. Thanks!