VLOOKUP with time range conditions?

R

RyanHillAZ

New Member
Joined
Oct 31, 2014
Messages
10
Hello Everyone,

I am trying to figure out how to write a formula to verify if a bus made it to the scheduled stop within a given window of time.

My data shows the bus schedule and it also shows any bus that arrived within the given landmark (geo-fence) within a period of time.

I would like to be able to say "If any vehicle arrived within the landmark stop (ex. 411 Stop C @ 9:15 AM) at least 30 minutes prior to 9:15 AM and within 5 minutes after 9:15 AM."

I am not even close to figuring this one out. The nearest reference that I could find on the message board was the post of "VLOOKUP with Time Range" - http://www.mrexcel.com/forum/excel-questions/384955-vlookup-time-range.html

But I am not proficient with INDEX or MATCH functions.

Here is some of my sample data: This comes from a report showing when the bus actually arrived within the given landmark location.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]VEHICLE[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"]Landmark[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]First In[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]Last In[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701
[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]5:42 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]5:47 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]6:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]6:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:13 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]8:42 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]8:46 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]9:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]9:21 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:13 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:17 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]11:45 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]11:47 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Here is the route information as to when the bus is supposed to be at the stop location.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Direction[/TD]
[TD]Landmark[/TD]
[TD]Scheduled Time[/TD]
[TD]Actual Time[/TD]
[TD](+,-)[/TD]
[TD]Decimal[/TD]
[TD](Y,N)[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]9:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD]=D2-C2[/TD]
[TD]=E2[/TD]
[TD]On Time?[/TD]
[TD]=IF((F2="Y"),1,IF((F2="N"),0))[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]10:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD]=D3-C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]1:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]2:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]5:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]9:15 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]10:15 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]1:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]2:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]5:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Columns E, F, G, & H can be ignored in the above example. They are basically helper columns to convert the time into a decimal and identify if the bus made the stop within a given time.

The full data excel can be found on my google drive link if you would like to see the full landmark history sheet and the validation worksheet that I am working to create.

Excel Link: https://drive.google.com/file/d/0B-z07hmwXXmIeFJCaHp0V0tDVms/view?usp=sharing

Thanks so much!

Ryan Hill
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
RyanHillAZ said:
Hello Everyone,

I am trying to figure out how to write a formula to verify if a bus made it to the scheduled stop within a given window of time.

My data shows the bus schedule and it also shows any bus that arrived within the given landmark (geo-fence) within a period of time.

I would like to be able to say "If any vehicle arrived within the landmark stop (ex. 411 Stop C @ 9:15 AM) at least 30 minutes prior to 9:15 AM and within 5 minutes after 9:15 AM."

I am not even close to figuring this one out. The nearest reference that I could find on the message board was the post of "VLOOKUP with Time Range" - http://www.mrexcel.com/forum/excel-questions/384955-vlookup-time-range.html

But I am not proficient with INDEX or MATCH functions.

Here is some of my sample data: This comes from a report showing when the bus actually arrived within the given landmark location.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]VEHICLE[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"]Landmark[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]First In[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]Last In[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]5:42 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]5:47 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]6:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]6:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:13 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]8:42 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]8:46 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]9:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]9:21 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:13 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:17 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]11:45 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]11:47 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Here is the route information as to when the bus is supposed to be at the stop location.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Direction[/TD]
[TD]Landmark[/TD]
[TD]Scheduled Time[/TD]
[TD]Actual Time[/TD]
[TD](+,-)[/TD]
[TD]Decimal[/TD]
[TD](Y,N)[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]9:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD]=D2-C2[/TD]
[TD]=E2[/TD]
[TD]On Time?[/TD]
[TD]=IF((F2="Y"),1,IF((F2="N"),0))[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]10:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD]=D3-C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]1:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]2:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]5:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]9:15 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]10:15 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]1:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]2:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]5:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Columns E, F, G, & H can be ignored in the above example. They are basically helper columns to convert the time into a decimal and identify if the bus made the stop within a given time.

The full data excel can be found on my google drive link if you would like to see the full landmark history sheet and the validation worksheet that I am working to create.

Excel Link: https://drive.google.com/file/d/0B-z07hmwXXmIeFJCaHp0V0tDVms/view?usp=sharing

Thanks so much!

Ryan Hill
Click to expand...

Hi Ryan,

Haven't been able to finish this, so i would though i would show what I had done and see it someone can come up with a formal which works.

First you need to custom format the cell with this: (got this at https://support.office.com/en-ca/ar...-of-time-9a6558a1-a86b-41d4-b244-1000fc7d31e9)

On the Home tab, in the Cells group, click Format, and then click Format Cells.

In the Format Cells dialog box, click the Number tab.

Under Category, click Custom.

In the Type box, type [h]:mm.

TIP You can also show the results in minutes and seconds by setting the format to [m]:ss, or minutes only by typing [m].

Click OK.

Excel displays 28:15 if you used the format [h]:mm).

Then a simple =D2-C2 to get the time from the actual arrival to Scheduled Time (Format this column with the custom format as well)

Now have tried all sorts of AND and IF functions but can't get it to work.

It doesn't seem to like the 0:30 in anything I know. Let see it someone can figure it out!
 
Upvote 0
Actually you need to use an ABS function in case the bus comes really early.

=ABS(C14-D14) will do it.

Next try this formula =IF(COUNT(E14),IF(E14>TIME(0,30,0),"Not On Time","On Time"),"")

Its working for me, but I have tried so many equations I have gone cabin crazy!

If it doesn't will go back to the drawing board!
 
Upvote 0
Hi Cosmic Wizard,

Thank you so much for all of your work on this! I am not sure where to place the formulas you mentioned.

1. =ABS(C14-D14) where does this one go? What does it accomplish?

2. =IF(COUNT(E14),IF(E14>TIME(0,30,0),"Not On Time","On Time"),"") where does this one go?

Thanks,

Ryan
 
Upvote 0
RyanHillAZ said:
Hi Cosmic Wizard,

Thank you so much for all of your work on this! I am not sure where to place the formulas you mentioned.

1. =ABS(C14-D14) where does this one go? What does it accomplish?

2. =IF(COUNT(E14),IF(E14>TIME(0,30,0),"Not On Time","On Time"),"") where does this one go?

Thanks,

Ryan
Click to expand...

Here we go:

Scheduled Time Actual Time Difference Result

9:16 AM 9:26 AM 0:10 On Time
 
Upvote 0
Cosmic Wizard said:
Here we go:

Scheduled Time Actual Time Difference Result

9:16 AM 9:26 AM 0:10 On Time
Click to expand...


Gotcha, that makes sense.

The part that I am still having trouble with is finding a formula which can review the data in the other table to determine time at which the bus entered the landmark zone from the other spreadsheet.

I am hoping there is a formula which can review the name of the landmark. [411 Stop C] then check the scheduled arrival time [9:19am] and review the data in the other spreadsheet to return a value of the nearest time to the scheduled time.
 
Upvote 0
RyanHillAZ said:
Gotcha, that makes sense.

The part that I am still having trouble with is finding a formula which can review the data in the other table to determine time at which the bus entered the landmark zone from the other spreadsheet.

I am hoping there is a formula which can review the name of the landmark. [411 Stop C] then check the scheduled arrival time [9:19am] and review the data in the other spreadsheet to return a value of the nearest time to the scheduled time.
Click to expand...

Just a couple of quick questions;

Do you mean to say you just want a count of the number of times a bus was on time at Stop C??

If so a simple COUNTIF function will do:

On Time =COUNTIF(F2:F20,D24) where cell D24 has the text On Time in it; and F2:F20 is the column where the result are in

Not On Time =COUNTIF(F4:F22,D26) same as above.

Or you could use Data Validation so you can swap between Stops to find the data.

=COUNTIFS(F2:F20,D24,B2:B20,D28) is the formula to use, where D28 is the cell where your validation list is, and B2:B20 is the range of bus stops for your validation list.

Your could also your data validation for your "On Time" and "Not On Time" choices, to simplified the process.
 
Upvote 0
Cosmic Wizard said:
Just a couple of quick questions;

Do you mean to say you just want a count of the number of times a bus was on time at Stop C??

If so a simple COUNTIF function will do:

On Time =COUNTIF(F2:F20,D24) where cell D24 has the text On Time in it; and F2:F20 is the column where the result are in

Not On Time =COUNTIF(F4:F22,D26) same as above.

Or you could use Data Validation so you can swap between Stops to find the data.

=COUNTIFS(F2:F20,D24,B2:B20,D28) is the formula to use, where D28 is the cell where your validation list is, and B2:B20 is the range of bus stops for your validation list.

Your could also your data validation for your "On Time" and "Not On Time" choices, to simplified the process.
Click to expand...


No, I am not looking for the count of On Time arrivals.

I will try to better explain the first issue.

I have the bus schedule in one spreadsheet. I have the number of times any number of different buses entered the landmark location in which the bus stop is located in the next spreadsheet.

I am trying to write a formula which will look at the bus schedule spreadsheet and review 2 variables. 1) The name of the bus stop 2) The scheduled time of arrival. I then want to take that information (Stop = 411 Stop 1, Scheduled Time = 9:19 AM) and review the landmark history information to find the following.

Return a time in which any bus entered the 411 Stop 1 within a time frame of 30 minutes before 9:19 AM and within 5 minutes after 9:19 AM. So the formula would be checking the range of cells within the landmark history spreadsheet to try and find the nearest time to that (if there is one) and return the time to my bus stop spreadsheet.

[TABLE="width: 677"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Direction[/TD]
[TD]Landmark[/TD]
[TD]Scheduled Time[/TD]
[TD]Actual Time[/TD]
[TD](+,-)[/TD]
[TD]Decimal [/TD]
[TD](Y,N)[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD]Northbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]9:19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:01:00[/TD]
[TD="align: right"]0.0007[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Northbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]10:19[/TD]
[TD][/TD]
[TD="align: right"]10:19:00[/TD]
[TD="align: right"]0.4299[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Northbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]13:19[/TD]
[TD][/TD]
[TD="align: right"]13:19:00[/TD]
[TD="align: right"]0.5549[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Northbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]14:19[/TD]
[TD][/TD]
[TD="align: right"]14:19:00[/TD]
[TD="align: right"]0.5965[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Northbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]17:19[/TD]
[TD][/TD]
[TD="align: right"]17:19:00[/TD]
[TD="align: right"]0.7215[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Northbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]18:19[/TD]
[TD][/TD]
[TD="align: right"]18:19:00[/TD]
[TD="align: right"]0.7632[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Southbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]9:13[/TD]
[TD][/TD]
[TD="align: right"]9:13:00[/TD]
[TD="align: right"]0.3840[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Southbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]10:13[/TD]
[TD][/TD]
[TD="align: right"]10:13:00[/TD]
[TD="align: right"]0.4257[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Southbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]13:13[/TD]
[TD][/TD]
[TD="align: right"]13:13:00[/TD]
[TD="align: right"]0.5507[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Southbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]14:13[/TD]
[TD][/TD]
[TD="align: right"]14:13:00[/TD]
[TD="align: right"]0.5924[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Southbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]17:13[/TD]
[TD][/TD]
[TD="align: right"]17:13:00[/TD]
[TD="align: right"]0.7174[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Southbound[/TD]
[TD]411 Stop 1[/TD]
[TD="align: right"]18:13[/TD]
[TD][/TD]
[TD="align: right"]18:13:00[/TD]
[TD="align: right"]0.7590[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Southbound[/TD]
[TD]411 Stop 10[/TD]
[TD="align: right"]6:21[/TD]
[TD][/TD]
[TD="align: right"]6:21:00[/TD]
[TD="align: right"]0.2646[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Southbound[/TD]
[TD]411 Stop 10[/TD]
[TD="align: right"]7:06[/TD]
[TD][/TD]
[TD="align: right"]7:06:00[/TD]
[TD="align: right"]0.2958[/TD]
[TD]N[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1559"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Vehicle[/TD]
[TD]VIN[/TD]
[TD]Serial Number[/TD]
[TD]Driver[/TD]
[TD]Landmark[/TD]
[TD]First In[/TD]
[TD]First In[/TD]
[TD]Last In[/TD]
[TD]Still In[/TD]
[TD]Distance (miles)[/TD]
[TD]Time Inside (hours)[/TD]
[TD]Idling Inside (hours)[/TD]
[TD]Stopped Inside (hours)[/TD]
[TD]First Odometer[/TD]
[TD]Last Odometer[/TD]
[TD]Heading[/TD]
[/TR]
[TR]
[TD="align: right"]765[/TD]
[TD]1FDFE4FS9DDB19476[/TD]
[TD="align: right"]4641293469[/TD]
[TD]not set[/TD]
[TD]411 stop 1[/TD]
[TD="align: right"]5/26/15 12:00 AM[/TD]
[TD="align: right"]10:12[/TD]
[TD="align: right"]10:12 AM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75,512.50[/TD]
[TD="align: right"]75,512.50[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]765[/TD]
[TD]1FDFE4FS9DDB19476[/TD]
[TD="align: right"]4641293469[/TD]
[TD]not set[/TD]
[TD]411 stop 1[/TD]
[TD="align: right"]5/26/15 12:00 AM[/TD]
[TD="align: right"]13:15[/TD]
[TD="align: right"]1:15 PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75,555.60[/TD]
[TD="align: right"]75,555.60[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]765[/TD]
[TD]1FDFE4FS9DDB19476[/TD]
[TD="align: right"]4641293469[/TD]
[TD]not set[/TD]
[TD]411 stop 1[/TD]
[TD="align: right"]5/26/15 12:00 AM[/TD]
[TD="align: right"]14:18[/TD]
[TD="align: right"]2:18 PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75,575.90[/TD]
[TD="align: right"]75,575.90[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]765[/TD]
[TD]1FDFE4FS9DDB19476[/TD]
[TD="align: right"]4641293469[/TD]
[TD]not set[/TD]
[TD]411 stop 1[/TD]
[TD="align: right"]5/26/15 12:00 AM[/TD]
[TD="align: right"]17:10[/TD]
[TD="align: right"]5:10 PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75,618.60[/TD]
[TD="align: right"]75,618.60[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]767[/TD]
[TD]1FDFE4FS6DDB27454[/TD]
[TD="align: right"]4641294457[/TD]
[TD]not set[/TD]
[TD]411 stop 1[/TD]
[TD="align: right"]5/26/15 12:00 AM[/TD]
[TD="align: right"]7:06[/TD]
[TD="align: right"]7:06 AM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]97,109.60[/TD]
[TD="align: right"]97,109.60[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]767[/TD]
[TD]1FDFE4FS6DDB27454[/TD]
[TD="align: right"]4641294457[/TD]
[TD]not set[/TD]
[TD]411 stop 1[/TD]
[TD="align: right"]5/26/15 12:00 AM[/TD]
[TD="align: right"]11:12[/TD]
[TD="align: right"]11:12 AM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]97,222.20[/TD]
[TD="align: right"]97,222.20[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]767[/TD]
[TD]1FDFE4FS6DDB27454[/TD]
[TD="align: right"]4641294457[/TD]
[TD]not set[/TD]
[TD]411 stop 1[/TD]
[TD="align: right"]5/26/15 12:00 AM[/TD]
[TD="align: right"]12:16[/TD]
[TD="align: right"]12:16 PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]97,257.00[/TD]
[TD="align: right"]97,257.00[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]767[/TD]
[TD]1FDFE4FS6DDB27454[/TD]
[TD="align: right"]4641294457[/TD]
[TD]not set[/TD]
[TD]411 stop 1[/TD]
[TD="align: right"]5/26/15 12:00 AM[/TD]
[TD="align: right"]18:33[/TD]
[TD="align: right"]6:33 PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]97,428.00[/TD]
[TD="align: right"]97,428.00[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
RyanHillAZ said:
Return a time in which any bus entered the 411 Stop 1 within a time frame of 30 minutes before 9:19 AM and within 5 minutes after 9:19 AM. So the formula would be checking the range of cells within the landmark history spreadsheet to try and find the nearest time to that (if there is one) and return the time to my bus stop spreadsheet.
Click to expand...

Stop if I am on the wrong tract, but you want the nearest time to 9:19 over say the course of a day, 30 mins before and 5 mins after.

So basically one time which is the closest to 9:19, like 9:21???
 
Upvote 0
Cosmic Wizard said:
Stop if I am on the wrong tract, but you want the nearest time to 9:19 over say the course of a day, 30 mins before and 5 mins after.

So basically one time which is the closest to 9:19, like 9:21???
Click to expand...

Yes, that is exactly what I am looking for. Thanks so very much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
437
Peter_SSs
Peter_SSs
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
254
etaf
etaf
J
  • Question Question
Finding Numbers from a Column Using Target Numbers
2
Replies
11
Views
414
Cubist
Cubist
V
  • Question Question
Column Output 2
Replies
7
Views
327
vmjan02
V
D
  • Solved
IF and VLOOKUP to calculate time zone times
Replies
3
Views
452
etaf
etaf

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top