Function Problems

How to Find the Next Available Date and Time Window in Excel

Nimra MalikNimra Malik Nov 10, 2026 870 views

Question details

The user needs to create an Excel formula to identify the next available open time slot based on a target date and time.

How to Find the Next Available Date and Time Window in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Scheduling logistics, such as finding the next available opening window for a ship when the target time might fall outside the current open windows.
Observed behavior
A structured output is required to accurately calculate and display the next 'open' and corresponding 'close' timestamps from a given scheduling data table.
Before you start

Ensure your scheduling data is formatted as a structured Excel table (e.g., 'Table1') with clear columns for 'Window' (Date/Time) and 'Status' (open/close). Verify that your Excel version supports the LET function, which is available in Excel 2021 or Microsoft 365.

Solution 1Recommended

Use MINIFS and LET to Calculate the Next Open Window

This method uses the MINIFS function to look up the earliest time slot matching the 'open' status that occurs strictly after the requested date and time.

By combining the MINIFS function with LET, you can create a clean, efficient formula that calculates the next available date and time without duplicating complex logic. This prevents Excel from returning a default '0' if no time slot is found.

1
Prepare your data table

Organize your schedule into an Excel table named 'Table1'. Ensure it has a column named 'Window' containing date and time values, and a column named 'Status' marked strictly as either 'open' or 'close'.

2
Input target date and time

Enter your target search Date in cell B1 and the specific Time in cell B2.

3
Apply the 'Next Open' formula

In the cell where you want the result, enter the following formula: `=LET(nextOpen, MINIFS(Table1[Window], Table1[Status], "open", Table1[Window], ">="&B2+B1), IF(nextOpen=0, "No slot found", nextOpen))`.

4
Format the result

Select the result cell, press Ctrl+1 to open the Format Cells dialog, and apply a Custom Date/Time format (e.g., 'mm/dd/yyyy hh:mm AM/PM') to display the window correctly.

Use MINIFS and LET to Calculate the Next Open Window
Version Compatibility: The LET function is available in Microsoft 365 and Excel 2021. If you are using an older version, you can achieve the same result by repeating the MINIFS formula inside an IF statement.

Easily Manage Scheduling Formulas with WPS Spreadsheet

WPS Spreadsheet provides full support for advanced functions like MINIFS and LET, allowing you to easily build complex scheduling and logistics tracking tools without expensive subscriptions.

  1. 1. Download and Install: Download WPS Office Free from the official website and install it on your Windows, Mac, or Linux device.
  2. 2. Open your Schedule: Launch WPS Spreadsheet and open your existing .xlsx schedule tracker.
  3. 3. Apply the MINIFS formula: Select your target cell, paste the MINIFS formula to find your next available time window, and press Enter to instantly calculate the logistical results.
Fully compatible with Microsoft Excel formulas, including MINIFS, LET, and dynamic arrays.Seamlessly open, edit, and save .xlsx workbooks without formatting loss.Lightweight architecture ensures it runs smoothly even with massive scheduling datasets.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my MINIFS formula return a 0 instead of a date?

MINIFS returns 0 if no records match your specified criteria. Because Excel stores dates as serial numbers starting from 1900, the value 0 is visually formatted as January 0, 1900. Wrapping your formula in an IF or LET statement allows you to replace the 0 with custom text like 'No slot found'.

Can I use this formula if my dates and times are in separate columns?

Yes, but you will need to modify your data structure. The easiest method is to create a 'Helper Column' in your table that combines the Date and Time columns (e.g., `=[@Date]+[@Time]`). You can then run the MINIFS formula directly against this new combined helper column.

What if my version of Excel doesn't support the LET function?

If you are using Excel 2019 or earlier, you can bypass the LET function by typing the MINIFS portion twice. The formula would look like this: `=IF(MINIFS(...) = 0, "No slot found", MINIFS(...))`. This works exactly the same but requires slightly more processing power.

How do I fix a #NAME? error in this formula?

The #NAME? error typically occurs if your Excel version does not recognize the LET or MINIFS functions, or if the table name (e.g., Table1) or column names (e.g., [Window]) are misspelled. Verify your function compatibility and ensure your data range is properly formatted as a Table via Insert > Table.