How to Return Yes, At Risk, or No Based on Excel Date Ranges in WPS Office
Question details
The user needs an Excel formula in WPS Office Spreadsheets that evaluates approval and renewal dates and returns Yes, At Risk, or No based on the renewal period.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user manages approval and renewal dates and wants an automatic status column that changes based on the current date.
- Observed behavior
- The formula should return a blank when no renewal date exists, No when the date has expired, At Risk when the renewal date is within four months, and Yes otherwise.
Open your workbook in WPS Office Spreadsheets and verify that renewal dates such as E16 are real date cells, not text. Identify the column where you want the Yes, At Risk, or No status to appear.
Use an IFS Formula with TODAY and EDATE to Return the Status
Best for a single dynamic formula that automatically refreshes as the current date changes and applies to one or many date rows.
The IFS function checks conditions from left to right: a blank renewal date returns blank, an expired date returns "No", a renewal date within four months returns "At Risk", and all remaining dates return "Yes". The condition E16<EDATE(TODAY(),4) catches all dates before the four-month threshold, so the E16<TODAY() condition must appear first to separate expired dates.
In WPS Office Spreadsheets, click the cell where you want the status result to appear, for example F16.
In the formula bar, type or paste: =IFS(E16="","",E16<TODAY(),"No",E16<EDATE(TODAY(),4),"At Risk",TRUE,"Yes").
Press Enter. If E16 contains a renewal date, the cell displays Yes, At Risk, or No; if E16 is blank, it remains blank.
Select F16, then drag the fill handle at the bottom-right corner down the column to apply the formula to the other rows.
Classify Renewal Dates as Yes, At Risk, or No in WPS Office
WPS Office Spreadsheets fully supports the IFS, TODAY, and EDATE functions, so you can build a dynamic approval and renewal date tracking formula without leaving the workbook.
- 1. Download WPS Office: Download WPS Office Free from the official WPS website and install it on your Windows, Mac, Linux, or mobile device.
- 2. Open your Excel workbook: Launch WPS Office Spreadsheets and open the existing .xlsx or .xls workbook containing your approval and renewal dates.
- 3. Enter the status formula: Click the first status cell, for example F16, and enter =IFS(E16="","",E16<TODAY(),"No",E16<EDATE(TODAY(),4),"At Risk",TRUE,"Yes").
- 4. Apply the formula to the remaining rows: Drag the fill handle from F16 down the column so each row automatically returns Yes, At Risk, or No based on its renewal date.
Frequently Asked Questions
Why does my formula return #NAME? in WPS Office Spreadsheets?
A #NAME? error usually means a function name is misspelled or your localized version uses different function names. In English WPS Office, type exactly IFS, TODAY, and EDATE, and use commas between arguments. Also verify that the cell containing the renewal date is a real date cell, not a text string.
Can I change the At Risk period from four months to three months?
Yes. Replace the number 4 in EDATE(TODAY(),4) with another number, such as EDATE(TODAY(),3), to flag dates within three months as At Risk. Keep the E16<TODAY() condition before the EDATE condition so expired dates still return No.
How do I apply this formula to an entire column of renewal dates?
Enter the formula in the first result cell, such as F16, then use the fill handle at the bottom-right corner to drag down through the column. You can also double-click the fill handle when the adjacent date column has data to auto-fill down.
Why does the formula return At Risk for a past date that should be No?
The conditions must be in the correct order. Put E16="" first, E16<TODAY() second, E16<EDATE(TODAY(),4) third, and TRUE last. Because the EDATE check includes past dates, placing it before the expiry check will incorrectly classify expired dates as At Risk.
