Fix Excel Formula Returning "Due Within 90 Days" for Past Dates
Question details
A nested IF formula used for tracking dates is evaluating conditions out of order, incorrectly returning a status of "Due Within 90 Days" for dates that have already passed.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic status tracker using formulas to categorize dates into statuses like "Current", "Due Within 90 Days", or "Past Due" based on a specific reference date cell.
- Observed behavior
- The formula correctly identifies future dates but incorrectly labels dates earlier than the reference date as "Due Within 90 Days" instead of displaying the expected "Past Due" status.
Ensure that both your reference date cell and the target date cells are formatted as Dates rather than Text, as Excel requires valid date serial numbers to perform accurate mathematical subtraction.
Reorder the Nested IF Conditions
Excel's IF function evaluates conditions sequentially and stops at the first TRUE result. Reordering the logic to check for the largest date differences first will correctly filter out past dates.
The issue stems from the order of the conditions in the original formula. When subtracting dates, past dates result in a negative number. If your formula checks for "<90" too early in the logic sequence, negative numbers will also trigger a TRUE result because any negative number is mathematically less than 90.
Click on the cell where you want the due date status to appear in your spreadsheet.
Type the updated formula: =IF(OR(R10={"New","Not Required"}),R10,IF(R10-$I$1>90,"Current",IF(R10-$I$1>=0,"Due Within 90 Days","Past Due")))
Press the Enter key to calculate the result. Click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply this correct logical sequence to the rest of your date column.
Easily Manage Nested Formulas in WPS Office
Writing and troubleshooting complex IF functions is much easier in WPS Spreadsheet. With built-in formula syntax hints and 100% compatibility with Excel date functions, you can track deadlines effortlessly.
- 1. Open your document: Launch WPS Office and open the spreadsheet containing your date tracking table.
- 2. Enter the IF formula: Select the status output cell and type =IF( to trigger smart formula hints.
- 3. Input the logic sequence: Follow the on-screen syntax guide to enter your >90 and >=0 conditions in the correct descending logical order.
- 4. Fill down the column: Hit Enter and double-click the cell's fill handle to apply the corrected formula instantly to all remaining rows.
Frequently Asked Questions
Why do past dates evaluate as less than 90 in Excel formulas?
In Excel, dates are stored as serial numbers. When you subtract a past date from a newer reference date, the result is a negative number. Since negative numbers are mathematically less than 90, any formula checking for `<90` will incorrectly capture past dates unless those negative numbers are filtered out by a previous IF condition.
What is the correct syntax for the Excel DAYS function?
The syntax is `DAYS(end_date, start_date)`. It returns the number of days between the two dates. If you mistakenly put the older date as the `end_date`, the function will return a negative number, which can disrupt your tracking logic.
How can I conditionally format "Past Due" dates in red?
Select the column containing your formula results. Go to the Home tab, click 'Conditional Formatting', choose 'Highlight Cells Rules', and select 'Equal To'. Type 'Past Due' into the box and choose the 'Light Red Fill with Dark Red Text' formatting option.
