SOLVED

Look for dates/times that are closest to specified date/time

Copper Contributor

Hello everyone,

 

In my worksheet, I have a list of events that I need to schedule flights for (both to and from the event).  The flights to be scheduled depend on the time of the event, and there is a large list to look through, so I want to try and automate this each month.

 

On one sheet, I have a list of the events that I want to schedule flights for.  Each event has a route, along with either the “latest arrival time” (for outbound flights), or “earliest departure time” (for return flights).  These times are compared to a full list of scheduled flights (Sheet 2).   I've also attached a sample with the expected results.

 

For the outbound flight:

  • For the listed route (column B), look at the ARRIVAL times for flights in Sheet 2
  • Return the flight (flt #, departure time, arrival time) for the flight is closest to – but no later than – the “out latest arrival” date/time (column c)

 

So for event A:  LAXPHX, with latest arrival time of 7/1 @ 20:30.  There is a LAXPHX flight that arrives at 20:39, but since that is after the “latest arrival”, the next earliest one would be flight # 5, with an arrival time of 19:44.  This information is returned to columns D-F on Sheet 1

 

For the return flight,

  • For the listed route (column H), look at the DEPARTURE times in Sheet 2
  • Return the flight (flt #, departure date/time, arrival date/time) for the flight that is closest to – but no earlier than – the “return earliest departure” date/time (column I)

 

The return for event A:  PHXLAX, earliest departure of 7/3 @ 5:45.  There is a PHXLAX flight that departs @ 5:30, but that is too early; the desired one is flt # 14, departing @ 6:25, and arriving @ 7:19.  This information is returned to columns J-L on Sheet 1.

 

Note:  if it will be simpler to put the scheduled flights for each route in different sheets, that will be fine!

 

Thank you for any guidance you can provide!

2 Replies
best response confirmed by richzip505 (Copper Contributor)
Solution

@richzip505 

First, please keep all scheduled flights on the same worksheet.  Doing otherwise would result in formulas that are more complicated and less efficient.


The solution in the attached workbook requires Excel 365 or Excel for the web.


Columns D cells will contain essentially the same formula (it can be copied down as needed), and the column J formulas are not much different. Read the _Info worksheet for more information.

 

Here's the formula from cell D7:

=LET( desired_route_useful_times, FILTER(Sheet2!$B$2:$D$500,
        (Sheet2!$A$2:$A$500 = B7) * (Sheet2!$D$2:$D$500 <= C7) ),
    sorted_flights, SORT(desired_route_useful_times, 3, -1),
    best_flight, TAKE(sorted_flights, 1),
    IFERROR( best_flight, "" )
)

I have used a maximum of about 500 rows.  If you have more flight data than that, those cell references can be readily changed.  Or better, consider replacing the range references with dynamic named ranges; unfortunately, they will be difficult to set up accurately if you include completely empty rows between each route, as in your sample data.

 

@SnowMan55   This looks to work perfectly!  I'll do some more evaluating and reply if I see any errors.  Thank you also for the explanations of the LET, FILTER, and TAKE commands-- these are all new ones to me!

Thank you so much!

1 best response

Accepted Solutions
best response confirmed by richzip505 (Copper Contributor)
Solution

@richzip505 

First, please keep all scheduled flights on the same worksheet.  Doing otherwise would result in formulas that are more complicated and less efficient.


The solution in the attached workbook requires Excel 365 or Excel for the web.


Columns D cells will contain essentially the same formula (it can be copied down as needed), and the column J formulas are not much different. Read the _Info worksheet for more information.

 

Here's the formula from cell D7:

=LET( desired_route_useful_times, FILTER(Sheet2!$B$2:$D$500,
        (Sheet2!$A$2:$A$500 = B7) * (Sheet2!$D$2:$D$500 <= C7) ),
    sorted_flights, SORT(desired_route_useful_times, 3, -1),
    best_flight, TAKE(sorted_flights, 1),
    IFERROR( best_flight, "" )
)

I have used a maximum of about 500 rows.  If you have more flight data than that, those cell references can be readily changed.  Or better, consider replacing the range references with dynamic named ranges; unfortunately, they will be difficult to set up accurately if you include completely empty rows between each route, as in your sample data.

 

View solution in original post