Formula Errors

How to Use an Excel Formula to Display VALID or EXPIRED Based on Dates

Kushani Nimanthika Sep 28, 2026 868 views

Question details

The user needs a formula to evaluate a range of dates, returning VALID if all dates are today or in the future, EXPIRED if any date is in the past, and remaining blank if no dates are entered.

How to Create an Excel Formula to Display VALID or EXPIRED Based on Dates

Product: Excel
Device & OS: not provided
Scenario: Tracking expiration dates across multiple columns and needing an automated status indicator that accurately handles empty cells.
Observed behavior: Requires a dynamic status cell that updates automatically based on the current date compared to the entered dates in cells C2 through F2.

Before you start

Ensure that the cells containing your dates are properly formatted as 'Date' rather than 'Text' so the formula can accurately calculate them against the current system date.

Solution 1Recommended

Use a Nested IF Formula with MAX, MIN, and TODAY Functions

This is the recommended and most robust method. It checks if the date range is empty first, keeping the status cell clean, before evaluating the dates against today's date.

By nesting the IF statements, you can set a condition to check for blank cells using the MAX function. If the maximum value is 0 (meaning all cells are empty), the formula returns a blank string. Otherwise, it proceeds to check the earliest date using the MIN function.

Select the status cell

Click on cell A2 (or wherever you want the status to be displayed) to make it active.

Enter the nested formula

Type the following formula exactly as shown: =IF(MAX(C2:F2)=0,"",IF(MIN(C2:F2)>=TODAY(),"VALID","EXPIRED"))

Apply the formula

Press Enter to execute the formula. If cells C2:F2 are empty, A2 will appear blank.

Copy the formula down

Click the small square at the bottom-right corner of cell A2 and drag it down to apply this logic to the rest of your list.

Use a Nested IF Formula with MAX, MIN, and TODAY Functions

How the logic works: The MIN(C2:F2)>=TODAY() portion ensures that even the oldest date in your selected range is greater than or equal to today. If even one date has passed, the MIN value drops below TODAY(), triggering the EXPIRED result.

Solution 2

Use a Basic IF and MIN Formula (No Blank Cell Handling)

Use this simpler alternative if your spreadsheet is guaranteed to always have dates populated in the target cells and you don't need to account for empty rows.

Advanced Spreadsheets Made Easy

Easily Track Expiration Dates Using WPS Spreadsheet

WPS Office provides a highly capable, free spreadsheet alternative that fully supports advanced logical and date functions like IF, MIN, MAX, and TODAY. You can seamlessly create dynamic trackers just as you would in Microsoft Excel without compatibility issues.

1. Open your document in WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load your expiration tracking file.
2. Select your status cell: Click on the cell where you want the VALID/EXPIRED status to be displayed (e.g., A2).
3. Insert the evaluation formula: In the formula bar, type: =IF(MAX(C2:F2)=0,"",IF(MIN(C2:F2)>=TODAY(),"VALID","EXPIRED"))
4. Fill the series: Press Enter, then double-click the fill handle in the bottom-right corner of the cell to instantly apply it to your entire column.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and standard formulas.Built-in intelligent formula autocomplete helps you avoid syntax errors.Lightweight, fast-loading, and completely free to use.Clean user interface that feels familiar, ensuring a zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula display EXPIRED when all cells are completely blank?

In spreadsheet software, blank cells used in a MIN function are evaluated as zero. In date format, zero corresponds to January 0, 1900. Because 1900 is firmly in the past, the MIN value is less than TODAY(), causing the formula to return EXPIRED. Wrapping your formula in an IF(MAX(range)=0, "", ...) check prevents this.

Can I automatically highlight the EXPIRED cells in red?

Yes, you can use Conditional Formatting. Select the column with your formulas, go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Type 'EXPIRED' in the box and select the red text/background format.

Do I need to update the TODAY() function manually every day?

No, the TODAY() function is volatile, meaning it automatically updates to the current system date every time you open the workbook or whenever the spreadsheet recalculates.

How do I change the formula to only check one cell instead of a range?

If you only need to check a single cell (e.g., C2), you can remove the MIN and MAX functions. The formula would simply become: =IF(C2="","",IF(C2>=TODAY(),"VALID","EXPIRED")).

Excel & Data Problems