Hello everyone,
I need some serious help. I would like help on a formula that allows me to match a persons name, the date and the time of a case that was created with the a persons name, the date of a call and in between the start time and end time of a call.
for example: the names below are the persons that created a case within a call they took. i am trying to get the specific case id
into the call where it was created (B),
A: case ids
Name Date Case created Case ID
Bill sanders 10/12/2017 5:55 PM 125366
james Mark 10/13/2017 8:27 AM 125368
John Smith 10/14/2017 10:35 AM 125369
B: calls where the case ID was created
Name Date start end Case ID
Bill sanders 10/12/2017 4:10 PM 6:20 PM
james Mark 10/13/2017 8:25 AM 9:22 AM
John Smith 10/14/2017 10:23 AM 11:21 AM
ideally, what I am looking for is, for the CASE ID for Bill Sanders to show up where the case created time falls between the start and end time of the call.
I apologize if I am not explaining it clearly, but its a bit complicated for me.
the formula I am using, which is not working is below:
i have gotten as far as matching the name and date with the case ID, but the difficulty i am having is matching those two with the time the case was created within the start and end time of the call.
this array: =INDEX($D$2:$D$4,MATCH(A2&B2,$F$2:$F$4&$G$2:$G$4,0) gets me the case ID by looking at the name and date
this array: =MATCH(C2,IF($H$2:$H$4<=C2,IF($I$2:$I$4>=C2,0),0 gets me the case ID by looking only at the time the case was created within the start and end time.
i am looking for a way to mix the two into one formula so it can match the name, date and time into the name, date and start and end time.
Bookmarks