Excel Date Validation Formula for Expired and Expiring Dates in WPS Office
Question details
The user needs a WPS Office Excel formula that returns Expired, Expiring, or a blank result based on a date in column E and today's date.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has dates in column E and wants to validate each date against today's date for expiration tracking.
- Observed behavior
- Desired result: the formula shows Expired for dates on or before today, Expiring for dates within the next 30 days, and blank for blank cells or dates outside that window.
Make sure the dates in column E are actual date values rather than text. Select a result cell where the formula status will appear and confirm that the sheet supports the TODAY and AND functions.
Use a Nested IF Formula to Return Expired, Expiring, or Blank
This is the direct formula solution when you need WPS Office Excel to validate a date in column E against today's date and a 30-day expiration window.
The formula first returns blank for an empty cell, then returns Expired for dates on or before today, and then returns Expiring for dates within the next 30 days. If your first date is in a different row, replace E65 with that cell reference.
Open WPS Office and load the workbook that contains expiration dates in column E.
Click the cell where you want the status to appear, for example F65 if the date is in E65.
Type =IF(ISBLANK(E65),"",IF(TODAY()>=E65,"Expired",IF(AND(E65>TODAY(),E65<=TODAY()+30),"Expiring",""))) and press Enter.
Drag the fill handle from the result cell down through all rows that contain dates in column E.
Date Validation Formula for Expired and Expiring Dates in WPS Office
Apply a nested IF formula in WPS Office Spreadsheets to automatically label expired and expiring dates. The same formula works with Microsoft Excel functions, so your .xlsx files remain familiar and compatible.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the Excel workbook that contains dates in column E.
- 2. Enter the formula: Select the result cell and type =IF(ISBLANK(E65),"",IF(TODAY()>=E65,"Expired",IF(AND(E65>TODAY(),E65<=TODAY()+30),"Expiring","")))
- 3. Adjust the referenced cell: Change E65 to the first date cell in your data if it is not E65.
- 4. Fill the formula down: Press Enter and drag the fill handle down to apply the formula to all date rows.
Frequently Asked Questions
Why does the formula return blank even though a date is in column E?
Usually the date is stored as text rather than a real date value. Select the cell, press Ctrl+1, set the Number format to Date, or re-enter the date so WPS Office Excel recognizes it.
How do I change the expiration window from 30 days to 60 or 90 days?
Replace the number 30 in the formula with the number of days you want. For example, use TODAY()+60 or TODAY()+90 in the AND condition.
Does the formula update automatically when the current date changes?
Yes. The TODAY() function recalculates when the workbook updates, so Expired and Expiring statuses change automatically based on the current date.
Can I apply this formula to an entire column without dragging the fill handle?
Yes. Enter the formula in the first result cell, press Ctrl+C to copy it, select the target range in the result column, and press Ctrl+V to paste.
