Help with match time formula please

EvoUK

New Member
Joined
Jul 1, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I m a relative newbie to excel and would really appreciate some assistance with a formula I'm playing with.

Example problem: I have a list of train times and am trying to get excel to load up the next train time from the list based on the current time.

The formula I am using is:
Excel Formula:
=INDEX($B$12:$B$83,MATCH(MIN(ABS($B$12:$B$83-$B$1)),ABS($B$12:$B$83-$B$1),0))

$B$12:$B$83 is the source list of times
$B$1 is the current time

Although the formula does match to the nearest train time from the list, sometimes this time is in the past (e.g. current time 21:10, but excel highlights 20:56 as the next train time as its the nearest match to the list).
Is there a way I can tweak the formula so that it matches to the next greatest timing from the list?

Thanks in advance

E
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Welcome to the MrExcel forum!

Try:

Book1
ABCD
1TimeTimeNext Time
210:0010:0510:45
310:05
410:45
511:04
612:15
712:16
Sheet4
Cell Formulas
RangeFormula
D2D2=XLOOKUP(C2+1/86400,A2:A7,A2:A7,"not found",1)
 
Upvote 0
Solution
Welcome to the MrExcel forum!

Try:

Book1
ABCD
1TimeTimeNext Time
210:0010:0510:45
310:05
410:45
511:04
612:15
712:16
Sheet4
Cell Formulas
RangeFormula
D2D2=XLOOKUP(C2+1/86400,A2:A7,A2:A7,"not found",1)
That worked brilliantly. Thank you so much!
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with matching TIME formula from a list [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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