Excel Formula to Return Valid or Expired Based on Two Dates
Question details
The user wants an Excel formula that checks two date cells against the current date, returning "Valid" if both dates are in the future, and "Expired" if at least one date has passed.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking expiration dates, memberships, or certifications where two distinct date criteria must remain valid simultaneously.
- Observed behavior
- The goal is to automate the status categorization of items as "Valid" or "Expired" dynamically based on today's system date.
Ensure that the date values in your reference cells are formatted as real Excel dates and not stored as text, as text dates will cause formula calculation errors.
Use IF, AND, and TODAY Functions
This method combines logical conditions to dynamically compare two reference dates against the current system date, returning a specific text output.
The IF function allows you to return one value if a condition is true and another if it's false. By nesting the AND function, you can evaluate multiple date cells at once. The TODAY function automatically retrieves the current date, ensuring your sheet is always up to date.
Click on the cell (for example, C2) where you want the "Valid" or "Expired" status to be displayed.
Type the formula: =IF(AND(A2>TODAY(), B2>TODAY()), "Valid", "Expired") into the formula bar. This assumes your two reference dates are located in cells A2 and B2.
Press the Enter key to calculate the result for the first row.
Click and drag the fill handle (the small square at the bottom-right corner of the cell) down the column to apply the formula to the rest of your data.
Seamlessly Calculate Expiration Dates in WPS Spreadsheet
WPS Spreadsheet provides full support for complex logical and date formulas, allowing you to track valid and expired entries accurately and efficiently.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your existing workbook containing the date records.
- 2. Verify data format: Highlight your date columns and ensure they are formatted as Date values via the Home tab.
- 3. Insert the logic formula: Type =IF(AND(A2>TODAY(), B2>TODAY()), "Valid", "Expired") in your target status cell.
- 4. Auto-fill the column: Double-click the fill handle on the cell's bottom-right corner to instantly evaluate all rows.
Frequently Asked Questions
Why does my formula return "Expired" even though both dates are in the future?
This usually happens when the spreadsheet reads the dates as text strings rather than numeric date values. To fix this, you can use the 'Text to Columns' feature under the Data tab, or multiply the text cells by 1 to convert them into real serial dates.
What if I only have one date cell to check instead of two?
If you only need to evaluate a single date (for example, in cell A2), you can remove the AND function. The simplified formula would be: =IF(A2>TODAY(), "Valid", "Expired").
How can I automatically highlight the "Expired" cells in red?
You can use Conditional Formatting. Select your results column, go to the Home tab, click 'Conditional Formatting', choose 'Highlight Cells Rules' > 'Text that Contains', enter 'Expired', and select a red fill format.
Can I change the formula to return different text like "Active" or "Inactive"?
Yes, you can customize the output text by changing the words inside the quotation marks. For example, use =IF(AND(A2>TODAY(),B2>TODAY()),"Active","Inactive") to update the status labels.
