How to Find the Minimum Balance Date in a Dynamic Date Range in WPS Spreadsheets
Question details
The user wants a formula-based method to return the date associated with the minimum balance in a dynamic date range that starts at today's date and ends at the last populated row, without using VBA or manually updating row references.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing a cash flow or balance table and needs to identify the date on which the lowest balance occurs from today onward while the table continues to grow.
- Observed behavior
- The desired formula should automatically expand to include new rows, filter out dates before today, and return the date matching the minimum balance.
Make sure your balance data is stored in an Excel Table with columns for Date and Balance, and that all date cells contain real date values instead of text. In WPS Spreadsheets, verify that your version supports dynamic array functions such as FILTER and XMATCH; if not, update to a recent WPS Office version.
Use an Excel Table with LET, FILTER, and XMATCH in WPS Spreadsheets
Use this method when you want a fully dynamic, non-VBA formula that automatically adjusts as new rows are added and always starts from today's date.
This formula creates two filtered arrays inside a LET function: one for dates and one for balances, both filtered from today onward. It then uses XMATCH to locate the minimum balance within the filtered balance array and INDEX to return the corresponding date.
Select the data including headers, go to the Insert tab, click Table, confirm My table has headers, and name the table CashFlow in the Table Design or Table Tools context tab.
Click the cell where you want the result, type =LET(d,FILTER(CashFlow[Date],CashFlow[Date]>=TODAY()),b,FILTER(CashFlow[Balance],CashFlow[Date]>=TODAY()),INDEX(d,XMATCH(MIN(b),b))), and press Enter.
Make sure the formula returns the expected date. If your table is not named CashFlow, replace CashFlow in the formula with your actual table name.
Use WPS Spreadsheets to Find the Minimum Balance Date from Today Forward
WPS Spreadsheets supports Excel-compatible dynamic array formulas, so you can build a non-VBA table formula that returns the date of the lowest balance from today to the last populated row.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx cash flow workbook, and confirm the data is arranged in Date and Balance columns.
- 2. Convert the range to a named table: Select the data, go to Insert > Table, and name the table CashFlow so structured references can be used in the formula.
- 3. Enter the dynamic formula: In an empty cell enter =LET(d,FILTER(CashFlow[Date],CashFlow[Date]>=TODAY()),b,FILTER(CashFlow[Balance],CashFlow[Date]>=TODAY()),INDEX(d,XMATCH(MIN(b),b))) and press Enter.
- 4. Confirm the returned date: Check that the cell shows the date of the lowest balance from today onward and that the formula updates when new rows are added.
Frequently Asked Questions
Does this formula work if my workbook was created in Microsoft Excel?
Yes. WPS Spreadsheets opens .xlsx workbooks and supports Excel-compatible structured references and dynamic array functions such as FILTER, XMATCH, LET, and INDEX, so the same formula can be used.
What should I do if FILTER or XMATCH is not available in my WPS version?
Update WPS Office to a recent version that includes dynamic array functions. You can also check the formula by typing =FILTER in a cell; if it is not recognized, your version needs an update.
How do I make the range automatically end at the last nonblank row?
Convert the data to an Excel Table. Table references such as CashFlow[Date] and CashFlow[Balance] automatically expand to include new rows, so the formula does not use fixed row numbers.
Can I use this formula if my balance column contains blanks or text?
The MIN and XMATCH functions work best when the Balance column contains only numeric values. Remove blanks or text from the Balance column, or adjust the FILTER condition to exclude nonnumeric entries.
Will the formula update automatically when today's date changes?
Yes. The TODAY() function is volatile, so the formula recalculates when the workbook opens or when a recalculation is triggered, and it will filter dates from the new current date.
