'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Return Today's Current Fortnight Balance Using Excel Formulas
Question details
The user needs an Excel formula to dynamically find and return the current fortnight's closing balance based on today's date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking repayment dates and closing balances where the user must automatically fetch the balance corresponding to the current fortnight period.
- Observed behavior
- The user seeks a goal state where a combination of TODAY, INDEX, MATCH, and INDIRECT functions locates the first date after today and pulls the balance from the immediately preceding date column.
Ensure your worksheet has the dates organized chronologically in a single row (e.g., F2 to AZ2) and the corresponding closing balances in a specific, consistent row beneath them.
Use INDIRECT, INDEX, and MATCH Functions Together
This solution uses a combination of lookup and reference functions to locate the first future date in your timeline and returns the closing balance from the preceding column.
By comparing your date row against the TODAY() function, MATCH can locate the first date greater than today. Passing this into the INDEX and COLUMN functions allows you to pinpoint the absolute column number, which you then subtract by 1 to get the current fortnight.
Finally, wrapping these inside the ADDRESS and INDIRECT functions turns the row and column numbers into a valid cell reference that extracts the closing balance.
Click on the cell where you want the current fortnight's closing balance to be displayed.
Type the formula: =INDIRECT(ADDRESS(31,INDEX(COLUMN(F2:AZ2),MATCH(TRUE,F2:AZ2>TODAY(),0))-1)) into the formula bar.
Press Enter to execute. Note: If you are using an older version of Excel that does not support dynamic arrays, you must press Ctrl+Shift+Enter to evaluate the array correctly.
Calculate Dynamic Balances Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including TODAY, INDEX, MATCH, and INDIRECT. You can seamlessly manage complex financial records and track dynamic fortnight balances without any hassle.
- 1. Open your financial tracker: Launch WPS Spreadsheet and open your document containing the dates and balances.
- 2. Select the output cell: Click on the specific cell where you wish to display the fortnight balance.
- 3. Apply the lookup formula: Paste the exact formula =INDIRECT(ADDRESS(31,INDEX(COLUMN(F2:AZ2),MATCH(TRUE,F2:AZ2>TODAY(),0))-1)) into the formula bar and press Enter.
- 4. View your results: WPS Spreadsheet will instantly calculate and display the updated balance based on today's dynamic date.
Frequently Asked Questions
Why is my MATCH function returning an #N/A error?
The MATCH function returns an #N/A error if it cannot find a date in your specified range that is strictly greater than today's date. Ensure your date range extends far enough into the future.
Do I need to press Ctrl+Shift+Enter for this formula to work?
If you are using an older version of spreadsheet software without dynamic array support, you must press Ctrl+Shift+Enter. This allows the software to properly evaluate the F2:AZ2>TODAY() array comparison.
Can I use XLOOKUP instead of INDEX and MATCH for this task?
Yes, if your software supports XLOOKUP, you can search for TODAY(), set the match mode to 'Next larger item' (1), and return the corresponding balance from the offset column, which significantly simplifies the formula syntax.
