Reference look up with Date Associations

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
176
Hello,

On [Sheet 1] I have a listing of certain Customers, Customer IDs, and Times within my population. On [Sheet 2] I have a complete listing of all the Customer IDs and an Event Time.

[Sheet 1]

Customer
Customer ID
Time (TIMESTAMP)
Looking to see if a Event Time occurred within 180 days prior to the [Time] column (column C).
Looking to see if a Event Time occurred within 4 days after the [Time] column (column C).
Mr X
1
12/13/2017 9:21 AM
Y
N
Mr XX
2
12/13/2017 10:33 AM
N
Y
Mrs Y
3
12/13/2017 8:56 PM
N
Y
Ms Z
4
12/13/2017 2:31 AM
Y
Y

<tbody>
</tbody>

[Sheet 2]

Sheet 2 contains every single record of each of the four Customer IDs and the Event Time.

Customer ID
Event Time
1
12/13/2017 9:20 AM
1
8/4/2016 10:22 AM
1
6/12/2017 5:35 PM
2
1/1/2018 11:24 AM
2
12/15/2017 8:59 PM
2
2/12/2018 6:35 AM
3
12/14/2017 4:55 PM
4
9/1/2017 7:33 AM
4
12/16/2017 8:10 AM

<tbody>
</tbody>

So what I am asking is to create to column on [Sheet 1] - the ones with bold headers. Look at each record on [Sheet 1]; and tell me if, for that said Customer ID, they had an event happen within 180 days prior to the [Time] column. Also, look at each record on [Sheet 1]; and tell me if, for that said Customer ID, they had an even happen within 4 days after the [Time] column.

I hope this make sense.... Time is a factor as well.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,096,303
Messages
5,449,551
Members
405,569
Latest member
deanro

This Week's Hot Topics

Top