Hello,
[Sheet 1] as two columns. Column A [ID] and Column TIME
[Sheet 2] as two columns. Column A [ID] and Column TIME
[Sheet 2] holds all historical date for every ID and every occurrence in time that the ID performed a specific action.
1. What I am trying to create in [Sheet 1].[Column C] is to enter a formula to ask what is the closest date/time in Sheet 2 without being greater than date/time in Sheet 1 for each ID / TIME combination found on Sheet 1.
2. What I am trying to create in [Sheet 1].[Column D] is to enter a formula to ask what is the closest date/time in Sheet 2 with is greater than date/time in Sheet 1 for each ID / TIME combination found on Sheet 1.
[Sheet 1]
<tbody>
</tbody>
[Sheet 2]
<tbody>
</tbody>
[Sheet 1] as two columns. Column A [ID] and Column TIME
[Sheet 2] as two columns. Column A [ID] and Column TIME
[Sheet 2] holds all historical date for every ID and every occurrence in time that the ID performed a specific action.
1. What I am trying to create in [Sheet 1].[Column C] is to enter a formula to ask what is the closest date/time in Sheet 2 without being greater than date/time in Sheet 1 for each ID / TIME combination found on Sheet 1.
2. What I am trying to create in [Sheet 1].[Column D] is to enter a formula to ask what is the closest date/time in Sheet 2 with is greater than date/time in Sheet 1 for each ID / TIME combination found on Sheet 1.
[Sheet 1]
ID | TIME | Closest Prior to Column B | Closest After Column B |
123 | 12/13/2017 8:15 AM | 10/15/2017 3:33 PM | 12/13/2017 8:20 AM |
123 | 12/25/2017 11:45 PM | 12/13/2017 8:20 AM | 12/26/2017 6:45 AM |
789 | 12/1/2017 9:30 AM | 12/1/2017 8:30 AM | 12/1/2017 10:30 AM |
<tbody>
</tbody>
[Sheet 2]
ID | TIME |
123 | 12/13/2017 8:20 AM |
123 | 12/26/2017 6:45 AM |
123 | 8/20/2017 10:25 PM |
123 | 7/15/2017 10:50 AM |
123 | 10/15/2017 3:33 PM |
789 | 12/1/2017 10:30 AM |
789 | 12/1/2017 8:30 AM |
789 | 12/2/2017 10:45 PM |
<tbody>
</tbody>