VLOOKUP with time range conditions?

RyanHillAZ · Jun 16, 2015

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.

VEHICLE

Landmark

First In

Last In

701

411 stop C

5:42 AM

5:47 AM

701

411 stop C

6:20 AM

6:20 AM

701

411 stop C

7:13 AM

7:16 AM

701

411 stop C

7:50 AM

7:50 AM

701

411 stop C

8:42 AM

8:46 AM

701

411 stop C

9:20 AM

9:21 AM

701

411 stop C

10:13 AM

10:17 AM

701

411 stop C

10:50 AM

10:50 AM

701

411 stop C

11:45 AM

11:47 AM

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

Direction

Landmark

Scheduled Time

Actual Time

(+,-)

Decimal

(Y,N)

%

Northbound

411 stop C

9:16 AM

? formula ?

=D2-C2

=E2

On Time?

=IF((F2="Y"),1,IF((F2="N"),0))

Northbound

411 stop C

10:16 AM

? formula ?

=D3-C3

Northbound

411 stop C

1:16 PM

? formula ?

Northbound

411 stop C

2:16 PM

? formula ?

Northbound

411 stop C

5:16 PM

? formula ?

Southbound

411 stop C

9:15 AM

? formula ?

Southbound

411 stop C

10:15 AM

? formula ?

Southbound

411 stop C

1:15 PM

? formula ?

Southbound

411 stop C

2:15 PM

? formula ?

Southbound

411 stop C

5:15 PM

? formula ?

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

Cosmic Wizard · Jun 16, 2015

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.

VEHICLE

Landmark

<tbody>
</tbody>

First In

<tbody>
</tbody>

Last In

<tbody>
</tbody>
701 411 stop C

5:42 AM

<tbody>
</tbody>

5:47 AM

<tbody>
</tbody>
701 411 stop C

6:20 AM

<tbody>
</tbody>

6:20 AM

<tbody>
</tbody>
701 411 stop C

7:13 AM

<tbody>
</tbody>

7:16 AM

<tbody>
</tbody>
701 411 stop C

7:50 AM

<tbody>
</tbody>

7:50 AM

<tbody>
</tbody>

<tbody>
</tbody>
701 411 stop C

8:42 AM

<tbody>
</tbody>

8:46 AM

<tbody>
</tbody>
701 411 stop C

9:20 AM

<tbody>
</tbody>

9:21 AM

<tbody>
</tbody>
701 411 stop C

10:13 AM

<tbody>
</tbody>

10:17 AM

<tbody>
</tbody>
701 411 stop C

10:50 AM

<tbody>
</tbody>

10:50 AM

<tbody>
</tbody>
701 411 stop C

11:45 AM

<tbody>
</tbody>

11:47 AM

<tbody>
</tbody>

<tbody>
</tbody>

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

Direction Landmark Scheduled Time Actual Time (+,-) Decimal (Y,N) %

Northbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

9:16 AM

<tbody>
</tbody>
? formula ? =D2-C2 =E2 On Time? =IF((F2="Y"),1,IF((F2="N"),0))

Northbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

10:16 AM

<tbody>
</tbody>
? formula ? =D3-C3

Northbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

1:16 PM

<tbody>
</tbody>
? formula ?

Northbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

2:16 PM

<tbody>
</tbody>
? formula ?

Northbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

5:16 PM

<tbody>
</tbody>
? formula ?

Southbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

9:15 AM

<tbody>
</tbody>

<tbody>
</tbody>
? formula ?

Southbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

10:15 AM

<tbody>
</tbody>
? formula ?

Southbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

1:15 PM

<tbody>
</tbody>
? formula ?

Southbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

2:15 PM

<tbody>
</tbody>
? formula ?

Southbound

<tbody>
</tbody>

411 stop C

<tbody>
</tbody>

5:15 PM

<tbody>
</tbody>
? formula ?

<tbody>
</tbody>

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

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!

Cosmic Wizard · Jun 17, 2015

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!

RyanHillAZ · Jun 17, 2015

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

Cosmic Wizard · Jun 17, 2015

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

Here we go:

Scheduled Time Actual Time Difference Result

9:16 AM 9:26 AM 0:10 On Time

RyanHillAZ · Jun 17, 2015

Cosmic Wizard said:
Here we go:

Scheduled Time Actual Time Difference Result

9:16 AM 9:26 AM 0:10 On Time

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.

Cosmic Wizard · Jun 17, 2015

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.

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.

RyanHillAZ · Jun 17, 2015

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.

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.

Direction	Landmark	Scheduled Time	Actual Time	(+,-)	Decimal	(Y,N)	%
Northbound	411 Stop 1	9:19		0:01:00	0.0007	Y	1
Northbound	411 Stop 1	10:19		10:19:00	0.4299	N	0
Northbound	411 Stop 1	13:19		13:19:00	0.5549	N	0
Northbound	411 Stop 1	14:19		14:19:00	0.5965	N	0
Northbound	411 Stop 1	17:19		17:19:00	0.7215	N	0
Northbound	411 Stop 1	18:19		18:19:00	0.7632	N	0
Southbound	411 Stop 1	9:13		9:13:00	0.3840	N	0
Southbound	411 Stop 1	10:13		10:13:00	0.4257	N	0
Southbound	411 Stop 1	13:13		13:13:00	0.5507	N	0
Southbound	411 Stop 1	14:13		14:13:00	0.5924	N	0
Southbound	411 Stop 1	17:13		17:13:00	0.7174	N	0
Southbound	411 Stop 1	18:13		18:13:00	0.7590	N	0
Southbound	411 Stop 10	6:21		6:21:00	0.2646	N	0
Southbound	411 Stop 10	7:06		7:06:00	0.2958	N	0

Vehicle	VIN	Serial Number	Driver	Landmark	First In	First In	Last In	Still In	Distance (miles)	Time Inside (hours)	Idling Inside (hours)	Stopped Inside (hours)	First Odometer	Last Odometer	Heading
765	1FDFE4FS9DDB19476	4641293469	not set	411 stop 1	5/26/15 12:00 AM	10:12	10:12 AM	0	0	0	0	0	75,512.50	75,512.50	N
765	1FDFE4FS9DDB19476	4641293469	not set	411 stop 1	5/26/15 12:00 AM	13:15	1:15 PM	0	0	0	0	0	75,555.60	75,555.60	N
765	1FDFE4FS9DDB19476	4641293469	not set	411 stop 1	5/26/15 12:00 AM	14:18	2:18 PM	0	0	0	0	0	75,575.90	75,575.90	N
765	1FDFE4FS9DDB19476	4641293469	not set	411 stop 1	5/26/15 12:00 AM	17:10	5:10 PM	0	0	0	0	0	75,618.60	75,618.60	N
767	1FDFE4FS6DDB27454	4641294457	not set	411 stop 1	5/26/15 12:00 AM	7:06	7:06 AM	0	0	0	0	0	97,109.60	97,109.60	N
767	1FDFE4FS6DDB27454	4641294457	not set	411 stop 1	5/26/15 12:00 AM	11:12	11:12 AM	0	0	0	0	0	97,222.20	97,222.20	N
767	1FDFE4FS6DDB27454	4641294457	not set	411 stop 1	5/26/15 12:00 AM	12:16	12:16 PM	0	0	0	0	0	97,257.00	97,257.00	N
767	1FDFE4FS6DDB27454	4641294457	not set	411 stop 1	5/26/15 12:00 AM	18:33	6:33 PM	0	0	0	0	0	97,428.00	97,428.00	N

Cosmic Wizard · Jun 17, 2015

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.

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???

RyanHillAZ · Jun 17, 2015

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???

Yes, that is exactly what I am looking for. Thanks so very much!

VLOOKUP with time range conditions?

RyanHillAZ

New Member

Excel Facts

Cosmic Wizard

Board Regular

Cosmic Wizard

Board Regular

RyanHillAZ

New Member

Cosmic Wizard

Board Regular

RyanHillAZ

New Member

Cosmic Wizard

Board Regular

RyanHillAZ

New Member

Cosmic Wizard

Board Regular

RyanHillAZ

New Member

Similar threads

Forum statistics

Share this page

VLOOKUP with time range conditions?

New Member

Excel Facts

Board Regular

Board Regular

New Member

Board Regular

New Member

Board Regular

New Member

Board Regular

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock