Find date/time that is between dates/times in another dataset

[MagiCarty]

So I have two sets of data that I need to match up.

Set 1:
Column A has "Vehicles" like 29, 21, 20, etc. Column B has "Date/time" of when that vehicle last sent data.

Set 2:
Column D has the "Arrival Date/Time" of when the vehicle entered a location. Column E has the "Departure Date/Time" of when the vehicle left the location. Column F is the "Location" where the vehicle was between the Arrival and Departure times. Column G has the "Vehicle" assignment again (29, 21, 20, etc).

I'm looking for a formula to tell me which Location the vehicle was at by using the date/time in column B to be between the date/times in columns D and E, AND to match the vehicle numbers.

I can provide actual data if this will help. I wish there was an easy way to embed actual excel data into these things. Many thanks for your helps.

 

[cyrilbrd]

So I have two sets of data that I need to match up.

Set 1:
Column A has "Vehicles" like 29, 21, 20, etc. Column B has "Date/time" of when that vehicle last sent data.

Set 2:
Column D has the "Arrival Date/Time" of when the vehicle entered a location. Column E has the "Departure Date/Time" of when the vehicle left the location. Column F is the "Location" where the vehicle was between the Arrival and Departure times. Column G has the "Vehicle" assignment again (29, 21, 20, etc).

I'm looking for a formula to tell me which Location the vehicle was at by using the date/time in column B to be between the date/times in columns D and E, AND to match the vehicle numbers.

I can provide actual data if this will help. I wish there was an easy way to embed actual excel data into these things. Many thanks for your helps.

Hi,

Given in A1:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl64 { text-align: center; }.xl65 { text-align: center; }.xl66 { text-align: center; }</style> [TABLE="width: 455"]
<tbody>[TR]
[TD="class: xl64, width: 65"]vehicles[/TD]
[TD="class: xl64, width: 65"]DT[/TD]
[TD="class: xl64, width: 65"]location is[/TD]
[TD="class: xl64, width: 65"]A DT[/TD]
[TD="class: xl64, width: 65"]D DT[/TD]
[TD="class: xl64, width: 65"]location[/TD]
[TD="class: xl64, width: 65"]vehicle[/TD]
[/TR]
[TR]
[TD="class: xl64"]29[/TD]
[TD="class: xl66"]1/1/13 6:10[/TD]
[TD="class: xl65"]X[/TD]
[TD="class: xl66"]1/1/13 6:00[/TD]
[TD="class: xl66"]1/1/13 6:30[/TD]
[TD="class: xl64"]X[/TD]
[TD="class: xl64"]29[/TD]
[/TR]
[TR]
[TD="class: xl64"]30[/TD]
[TD="class: xl66"]1/1/13 7:00[/TD]
[TD="class: xl65"]W[/TD]
[TD="class: xl66"]1/1/13 7:20[/TD]
[TD="class: xl66"]1/1/13 7:40[/TD]
[TD="class: xl64"]Y[/TD]
[TD="class: xl64"]30[/TD]
[/TR]
[TR]
[TD="class: xl64"]31[/TD]
[TD="class: xl66"]1/1/13 8:00[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]1/1/13 8:30[/TD]
[TD="class: xl66"]1/1/13 8:38[/TD]
[TD="class: xl64"]Z[/TD]
[TD="class: xl64"]31[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]1/1/13 6:50[/TD]
[TD="class: xl66"]1/1/13 7:05[/TD]
[TD="class: xl64"]W[/TD]
[TD="class: xl64"]30[/TD]
[/TR]
</tbody>[/TABLE]



Formula in C2 is =IFERROR(INDEX(Sheet3!$F$2:$F$5,MATCH(1,(B2 > Sheet3!$D$2:$D$5)*(B2 < Sheet3!$E$2:$E$5)*(A2=Sheet3!$G$2:$G$5),0)),"")
Ctrl + Shift + Enter not just Enter on a PC or Command + Return on a MAC.
<s heet3!$e$2:$e$5)*(a2="Sheet3!$G$2:$G$5),0)),"")

</s>

 

[MagiCarty]

My customer's data source is flawed, however this formula works perfectly. Thank you.

 

[cyrilbrd]

My customer's data source is flawed, however this formula works perfectly. Thank you.

Glad it worked for you.
Thanks for the feedback and good luck for cleaning up the database!