How to Add an Aged Condition to an Excel IF or IFS Formula in WPS Office
Question details
The user needs to correct an Excel date-classification formula that should label records as Yes, At Risk, No, or Aged based on a date in cell E4.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building or correcting a formula that classifies records by date into Aged, No, At Risk, or Yes.
- Observed behavior
- The current formula may use DATEDIF arguments in the wrong order or unclear nested IF logic, causing incorrect date labels or formula errors.
Open the workbook in WPS Spreadsheets and select the cell that contains the source date, such as E4. Confirm that the date is stored as a real Date value and not text, because DATEDIF and comparison operators require true date values.
Use the Correct DATEDIF Order with an IFS Formula
Use this solution when you want clearer logic and fewer nested parentheses. IFS evaluates the Aged condition first and then checks the remaining date ranges from most specific to least specific.
The DATEDIF function requires the earlier date first: DATEDIF(start_date, end_date, "Y"). Place E4 before TODAY(), and put the Aged check before the At Risk check so records older than two years are not mislabeled.
Click the cell where you want the classification result to appear, for example F4.
Type =IFS(DATEDIF(E4,TODAY(),"Y")>2,"Aged",E4<TODAY(),"No",E4<DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"At Risk",TRUE,"Yes") and press Enter.
Enter a date more than two years in the past, a recent past date, a date within the next three months, and a date further in the future in E4 to confirm the result changes to Aged, No, At Risk, or Yes.
Rewrite the Formula with Nested IF for Compatibility
Use this alternative if you need a nested IF version or your spreadsheet style avoids IFS. It produces the same Aged, No, At Risk, and Yes labels with an outer Aged test.
Add an Aged Condition to Your IF or IFS Formula in WPS Spreadsheets
WPS Spreadsheets supports the same DATEDIF, IF, and IFS functions as Excel. Enter the corrected formula in the Formula Bar, copy it down the column, and the date labels recalculate automatically without changing your file format.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the .xlsx workbook that contains the date-classification formula.
- 2. Select the result cell: Click the cell where the formula should be entered, such as F4, and open the Formula Bar.
- 3. Enter the corrected IFS formula: Paste =IFS(DATEDIF(E4,TODAY(),"Y")>2,"Aged",E4<TODAY(),"No",E4<DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"At Risk",TRUE,"Yes") and press Enter.
- 4. Copy the formula down: Use the fill handle to copy the formula to the remaining rows, and confirm that the labels match the dates in column E.
- 5. Save as .xlsx: Press Ctrl+S to save the workbook in the original Excel format.
Frequently Asked Questions
Why does my DATEDIF formula return #NUM! when comparing E4 and TODAY()?
DATEDIF requires the earlier date first. Use =DATEDIF(E4,TODAY(),"Y") so the start date E4 comes before TODAY(). If E4 is after TODAY(), the formula should handle future dates through the IF or IFS conditions instead of a standalone DATEDIF.
How do I add an Aged label to an existing IF formula?
Add it as the first condition in IFS: =IFS(DATEDIF(E4,TODAY(),"Y")>2,"Aged",E4<TODAY(),"No",E4<DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"At Risk",TRUE,"Yes"). In a nested IF, make it the outermost condition: =IF(DATEDIF(E4,TODAY(),"Y")>2,"Aged",IF(...)).
What date range does the At Risk condition cover?
At Risk covers dates that are not already Aged or No and fall within the next three months from today. The DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())) expression creates a date three months from the current date.
Can I use this formula in WPS Spreadsheets on mobile?
Yes. Open the workbook in WPS Office mobile, tap the result cell, enter or paste the same formula in the formula bar, and confirm. WPS Spreadsheets supports DATEDIF, IF, and IFS on mobile apps.
Why is my date not being recognized by the formula?
The date cell may be stored as text. Select the cell, set the number format to Date, and re-enter the date if necessary. Real date values are required for DATEDIF and comparison operators.
