How to Find the Next Available Date and Time Window in Excel
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.

- 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.
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.
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.
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'.
Enter your target search Date in cell B1 and the specific Time in cell B2.
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))`.
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.

Calculate the Corresponding Closing Time
Once you find the next open window, you can use a secondary formula to determine when that specific scheduling window closes.
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. Download and Install: Download WPS Office Free from the official website and install it on your Windows, Mac, or Linux device.
- 2. Open your Schedule: Launch WPS Spreadsheet and open your existing .xlsx schedule tracker.
- 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.

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.




