How to Calculate Next Review Date or Return TBD in Excel
Question details
The user wants to calculate future review dates using Excel formulas based on set frequencies (Annual, Biannual, Triannual) and return "TBD" if the frequency is labeled as such.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing review schedules where the next review date depends on varying intervals, requiring a formula that handles uncertain schedules gracefully without returning errors.
- Observed behavior
- The user needs an automated formula to handle conditional date calculations, adding specific months based on a text string, and outputting "TBD" or a blank cell for invalid data.
Ensure your source date cells are formatted as Dates and your frequency text exactly matches the terms used in the formula without any extra spaces (e.g., "Annual", "Biannual", "Triannual", "TBD").
Use a Nested IF, EDATE, and LOOKUP Formula
Combine the IF, IFERROR, EDATE, and LOOKUP functions to calculate the exact future date based on the frequency interval while handling "TBD" tags and errors automatically.
The EDATE function adds a specified number of months to a given date. By combining it with LOOKUP, you can dynamically assign the correct number of months to add (12 for Annual, 24 for Biannual, 36 for Triannual) depending on the text in your frequency column.
Wrapping this core calculation in an IF statement allows you to immediately return the text 'TBD' whenever the frequency is explicitly marked as 'TBD'. Finally, the IFERROR function acts as a safety net, ensuring that invalid, misspelled, or blank cells return a clean empty result rather than an ugly #N/A or #VALUE! error code.
Organize your spreadsheet so that the review frequency (e.g., Annual, Biannual, TBD) is in column I, and the last review date is in column J.
Select the cell where you want the next review date to appear and input the following formula: =IF(I2="TBD","TBD",IFERROR(EDATE(J2,LOOKUP(I2,{"Annual","Biannual","Triannual"},{12,24,36})),""))
Press Enter to apply the formula, then click and drag the small fill handle at the bottom-right corner of the cell down to copy the calculation to the rest of your dataset.
Select the newly calculated cells, right-click, choose 'Format Cells', navigate to the 'Number' tab, and select your preferred Date format so the result displays correctly.
Calculate Dates Efficiently with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and lookup functions like EDATE, IFERROR, and LOOKUP, making it incredibly easy to automate schedules and track review dates. It offers a fast, user-friendly interface that guarantees flawless formula execution.
- 1. Open your document in WPS Spreadsheet: Launch WPS Office and open your workbook containing the review schedule.
- 2. Input the date formula: Select your target cell, paste the IF and EDATE formula exactly as you would in Excel, and press Enter.
- 3. Format and save: Use the Home tab to format the column as a Short Date, drag down to fill the column, and save your file seamlessly as an .xlsx document.
Frequently Asked Questions
Why is my EDATE formula returning a 5-digit number instead of a date?
Spreadsheet software stores dates as sequential serial numbers. If your formula returns a number like 45000, simply select the cell, go to the Home tab, click the Number Format dropdown, and change it to "Short Date" or "Long Date".
Can I add more frequencies like 'Quarterly' or 'Monthly' to the formula?
Yes. You can expand the LOOKUP array. For example, add "Quarterly" and "Monthly" to the text array and their corresponding month values (3 and 1) to the number array. However, remember that the text array in the LOOKUP function must always be in alphabetical order: LOOKUP(I2,{"Annual","Biannual","Monthly","Quarterly","Triannual"},{12,24,1,3,36}).
Why does the formula return a completely blank cell for some rows?
The formula uses the IFERROR function to return a blank ("") whenever it encounters an error. This usually happens if the starting date cell is empty, or if the frequency cell contains a typo, trailing spaces, or a term not listed in the array.
Does this formula work in older versions of Excel and other spreadsheet software?
Yes, functions like IF, IFERROR, EDATE, and LOOKUP are standard functions available in Excel 2007 and newer. They are also fully supported in modern, free alternatives like WPS Office Spreadsheet.
