How to Look Up Next Travel Date for Staff Travel Schedule in Excel

If you are managing a team of staff who travel frequently for work, you may want to keep track of their travel schedule and know when they are next expected to travel. This can help you plan ahead, avoid conflicts, and communicate effectively with your team members.

One way to do this is to use Excel formulas to look up the next travel date for each staff member based on a list of dates and travel indicators. In this article, we will show you how to do this using two different approaches: one with an array formula and one with a built-in function.

The basic idea behind both approaches is to compare the list of dates with the current date and find the smallest date that is greater than or equal to the current date. This will give us the next travel date for each staff member.

To do this, we need to use two components: a logical test and a function to return the minimum value.

The logical test is:

date >= TODAY()

where date is the range of dates and TODAY() is a function that returns the current date. This test will return an array of TRUE or FALSE values, depending on whether each date is in the future or not.

The function to return the minimum value is either:

MIN(IF(logical_test, value_if_true))

or

MINIFS(range, criteria_range1, criteria1)

where logical_test is the test we just defined, value_if_true is the same range of dates, range is the range of dates, criteria_range1 is the range of travel indicators, and criteria1 is the value "T" that indicates a travel day.

The first function is an array formula, which means it needs to be entered with Ctrl + Shift + Enter. The second function is a built-in function that was introduced in Excel 2016 and later versions.

Procedures

To look up the next travel date for each staff member, we need to follow these steps:

1. Create a table with the staff names, the dates, and the travel indicators. For example:

where X means on-site and T means travel.

2. Create a column for the next travel date and enter the formula in the first cell. For example, in cell L2, enter either:

=MIN(IF($B2:$K2="T",$B$1:$K$1))

or

=MINIFS($B$1:$K$1,$B2:$K2,"T")

depending on which function you want to use.

3. Press Ctrl + Shift + Enter if you are using the array formula, or just Enter if you are using the built-in function.
4. Copy the formula down to the rest of the cells in the column.
5. Format the column as date.

The result should look like this:

Example

To illustrate how this works, let’s use a scenario and an example with real numbers.

Suppose we have a team of five staff who travel to different locations for work. Their travel schedule for the first 10 days of January 2024 is shown in the table below. The dates are in the format dd/mm/yyyy.

We want to find out when each staff member is next expected to travel, assuming today is 02/01/2024.

To do this, we can use either of the formulas we learned above. For example, in cell L2, we can enter:

=MIN(IF($B2:$K2="T",$B$1:$K$1))

and press Ctrl + Shift + Enter.

This formula will do the following:

• It will compare the range B2:K2 with the value "T" and return an array of TRUE or FALSE values:

{FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE}

• It will use the IF function to return the corresponding dates from the range B1:K1 if the value is TRUE, or FALSE if the value is FALSE:

{FALSE,FALSE,03/01/2024,FALSE,FALSE,FALSE,FALSE,FALSE,09/01/2024,FALSE}

• It will use the MIN function to ignore the FALSE values and return the smallest date value:

03/01/2024

This means that Alice’s next travel date is 03/01/2024.

We can copy the formula down to the rest of the cells in column L and format the column as date. The final result will look like this:

Other Approaches

The formulas we used above are not the only ones that can achieve the same result. There are other ways to look up the next travel date for each staff member, such as:

• Using the INDEX and MATCH functions instead of the MIN and IF functions. For example, in cell L2, we can enter:

=INDEX($B$1:$K$1,MATCH(TRUE,$B2:$K2="T",0))

and press Ctrl + Shift + Enter.

This formula will do the following:

• It will use the MATCH function to find the position of the first value that is equal to "T" in the range B2:K2, and return an array of numbers or errors:

{#N/A,#N/A,3,#N/A,#N/A,#N/A,#N/A,#N/A,9,#N/A}

• It will use the INDEX function to return the value in the range B1:K1 that corresponds to the position returned by the MATCH function:

{#N/A,#N/A,03/01/2024,#N/A,#N/A,#N/A,#N/A,#N/A,09/01/2024,#N/A}

• It will ignore the errors and return the smallest date value:

03/01/2024

• Using the FILTER and MIN functions instead of the MINIFS function. For example, in cell L2, we can enter:

=MIN(FILTER($B$1:$K$1,$B2:$K2="T"))

and press Enter.

This formula will do the following:

• It will use the FILTER function to return the values in the range B1:K1 that meet the condition that the corresponding values in the range B2:K2 are equal to "T":

{03/01/2024,09/01/2024}

• It will use the MIN function to return the smallest date value:

03/01/2024

These are some alternative ways to look up the next travel date for each staff member. You can choose the one that suits your preference and Excel version.