How to Find the Next Shipment Date by Product in Excel
Question details
The user needs an Excel formula to dynamically determine the next available shipment date for specific products, excluding past dates and entries with zero shipment quantity.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking product shipments using a matrix layout where dates are arranged in columns and products in rows, or analyzing data from a PivotTable.
- Observed behavior
- Requires a function to look up a specific product, filter out quantities that are zero and dates that are before today, and return the very next valid calendar date.
Ensure your shipment tracking data is formatted as a matrix where the top row contains your date values, the first column contains your product names, and the intersecting cells contain the numeric shipment quantities.
Use a Nested FILTER Formula to Extract the Next Valid Date
This method uses the modern FILTER function combined with TODAY() to extract the exact upcoming date where shipment quantities are greater than zero.
By nesting two FILTER functions, we first isolate the row corresponding to your specific product. The outer FILTER then applies two conditions: the quantity must be positive, and the date column must be greater than or equal to the current date.
Click on the cell where you want the upcoming shipment date to be displayed.
Type the formula: =@FILTER($B$1:$F$1,(FILTER($B$2:$F$13,$A$2:$A$13=$I2)>0)*($B$1:$F$1>=TODAY()),"No Shipment").
Modify the formula to match your sheet's layout: change $B$1:$F$1 to your actual date header row, $B$2:$F$13 to your grid of shipment quantities, $A$2:$A$13 to the list of products, and $I2 to the cell holding the product name you are currently checking.
Press Enter to execute the formula. You can then use the fill handle to drag the formula down if you need to calculate the next dates for a list of multiple products.

Calculate Logistics Dates Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER, allowing you to manage complex logistics, ignore past dates, and track inventory schedules accurately.
- 1. Open your inventory file: Launch WPS Spreadsheet and open your existing date-and-quantity layout workbook.
- 2. Input the dynamic formula: Select the target cell and type the nested FILTER formula to cross-reference your products and future dates.
- 3. Calculate instantly: Press Enter. WPS Spreadsheet processes the array formula instantly, accurately reflecting the next shipment date.

Frequently Asked Questions
Why does the formula return a #CALC! error?
The #CALC! error typically occurs when the FILTER function finds no records matching your criteria. In the provided formula, appending the "No Shipment" argument at the end instructs the function to return text instead of a #CALC! error if there are no future shipments scheduled.
Does this dynamic FILTER formula work in older versions of Excel?
The FILTER function is only available in Microsoft 365, Excel 2021, and modern spreadsheet alternatives like WPS Office. If you are using an older version, you must use a complex INDEX and MATCH array combination by pressing Ctrl+Shift+Enter.
How can I check for dates strictly after today, excluding today's shipments?
The formula evaluates today's date using the >=TODAY() condition. If you want to find shipments that occur exclusively on future dates, simply change the condition operator to >TODAY().




