Match function with time

[David Gladstone]

I am trying to use the MATCH function to find last train that I can
catch to reach my destination before, on time or just after a given
time.
The arrival times are E1:E10 and also named as ArriveTime and is
formatted HH:MM AM/PM
The target time in H1 and is also formatted as HH:MM AM/PM.
For test purposes I am using 10:00AM and ArriveTime contains
09:59,10:00 and 10:01AM
Using =MATCH($H$1,ArriveTime,0) or =MATCH($H$1,ArriveTime,1) work fine
and both return the row (5), but =MATCH($H$1,ArriveTime,-1) returns #N/
A instead of the row of the earliest train after the target.
I am using Excel 2007

 

[Pete_UK]

If you set the third MATCH parameter to -1 then ArriveTime will have
to be in reverse order.

I'm not sure what you mean by "...reach my destination before, on time
or just after a given time.", as this is very ambiguous - how long is
"just after" ? If this is 5 minutes, say, then you set your target
time 5 minutes later.

Oh, and you should be using a final parameter of 1, as you are not
looking for an exact match.

Hope this helps.

Pete

 

[David Gladstone]

If you set the third MATCH parameter to -1 then ArriveTime will have
to be in reverse order.

I'm not sure what you mean by "...reach my destination before, on time
or just after a given time.", as this is very ambiguous - how long is
"just after" ? If this is 5 minutes, say, then you set your target
time 5 minutes later.

Oh, and you should be using a final parameter of 1, as you are not
looking for an exact match.

Hope this helps.

Pete

Many thanks. I have now re-read the Excel Help file and feel rather
humble.

 

[Pete_UK]

Many thanks.  I have now re-read the Excel Help file and feel rather
humble

No need for that ..., but thanks for feeding back - glad to be of
help.

Pete