Function Problems

How to Mark a Date as Expired After One Year in Excel

Chanuka Geekiyanage Sep 30, 2026 868 views

Question details

The user needs an Excel formula to change text from 'Current' to 'Expired' when a reference date is at least one year old, while managing the limitation that manual text and formulas cannot share the same cell.

How to Mark a Date as Expired After One Year in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking the expiration of items or memberships where statuses need to update automatically based on a one-year timeframe.
Observed behavior: The goal is to correctly output 'Expired' or 'Current' into a cell based on an aging date without overwriting cells that already contain manually entered text.

Before you start

Ensure your reference cells are formatted as 'Date' rather than 'Text' in Excel, otherwise the EDATE formula will not be able to calculate the one-year difference correctly.

Solution 1Recommended

Use a Helper Column with IF and EDATE Formulas

Because a formula cannot exist in the same cell as manually entered data, the most reliable method is to create a separate 'Status' column exclusively for your formula results.

The EDATE function calculates a date a specific number of months in the future or past. By combining it with the IF and TODAY functions, Excel can dynamically check if exactly 12 months have passed since the original date.

Create a new column

Insert a new column next to the column containing your target dates. Label this new column 'Status' or 'Expiration'.

Enter the formula

Click on the first empty cell in your new column (e.g., B2) and type the formula: =IF(TODAY()>=EDATE(A2,12),"Expired","Current") . Replace 'A2' with the reference to your actual date cell.

Apply to the entire list

Press Enter to see the result. Then, click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of your rows.

Use a Helper Column with IF and EDATE Formulas

Formula Customization: You can change the number '12' in the EDATE function to any number of months to adjust the expiration period (e.g., '6' for half a year).

Solution 2

Apply the Formula Only to Blank Cells Using Go To Special

If your column already has manually entered data (like custom notes or overrides) and you only want to fill the remaining blank cells with the expiration formula.

Work smarter with WPS Office

Automate Date Tracking with WPS Spreadsheet

WPS Spreadsheet handles complex date functions smoothly. You can easily set up automated expiration trackers using the exact same formulas used in Excel, with seamless formatting and data management capabilities.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates you want to track.
2. Select the destination cell: Click on an empty cell in an adjacent column where you want the expiration status to be displayed.
3. Input the expiration formula: Type =IF(TODAY()>=EDATE(A2,12),"Expired","Current") and press Enter.
4. Use Go To Special for blanks: If dealing with mixed data, use Home > Find and Replace > Go To > Blanks to select empty cells, type your formula, and press Ctrl+Enter.

100% compatibility with Microsoft Excel formulas and .xlsx formatsSupports all advanced date functions including EDATE and TODAYIntuitive Go To Special features for bulk data handlingLightweight and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Can I put a formula and manually typed text in the same Excel cell?

No, an Excel cell can only contain either manually entered data or a formula, not both. If you type text into a cell that contains a formula, the formula will be permanently overwritten. You should use a separate helper column for formula outputs.

How do I calculate an expiration after exactly 365 days instead of one calendar year?

If you want to track exactly 365 days rather than matching the calendar month (like EDATE does), you can use basic subtraction: =IF(TODAY()-A2>=365, "Expired", "Current").

Why is my EDATE formula returning a #VALUE! error?

This error usually occurs because the referenced cell (e.g., A2) contains text instead of a valid Excel date. Check the original date cell, remove any unrecognized text characters, and ensure it is formatted as a Date.

How can I change the formula to expire a date after 3 years?

Since the EDATE function calculates based on months, you simply multiply the number of years by 12. For a 3-year expiration, use 36 months in the formula: =IF(TODAY()>=EDATE(A2,36),"Expired","Current").

Excel & Data Problems