Excel Formula to Mark a Warranty as Active or Expired by Date
Question details
Automatically display Active or Expired by comparing today's date with a product's warranty expiration date.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains warranty expiration dates and needs a formula-based status for each product.
- Observed behavior
- The warranty status should show Expired when today's date reaches or passes the expiration date, and Active before that date.
Identify the cell containing the warranty expiration date, such as D16, and choose a separate cell for the status. Confirm that the expiration date is stored as a valid spreadsheet date rather than text.
Use IF and TODAY to calculate warranty status
Use this formula when the warranty should be marked Expired on its expiration date and afterward.
Click the cell where you want the warranty status to appear, such as E16, if the expiration date is in D16.
Type =IF(TODAY()>=D16,"Expired","Active") and press Enter. Replace D16 with the cell that contains your warranty expiration date.
Select the formula cell and drag its fill handle down the status column. Relative references will adjust to the expiration date in each row.
In Excel desktop, go to Formulas > Calculation Options and select Automatic. TODAY() uses the current system date and updates when the workbook recalculates, such as when it is opened.
Handle missing dates and check incorrect results
Use a blank-cell check when some products have no expiration date, and verify date values if the result is unexpected.
Track Warranty Expiration Dates in WPS Spreadsheet
Use the same IF and TODAY formula in WPS Spreadsheet to calculate warranty status from an expiration-date column. Open an existing Excel workbook or create a new warranty tracker, then fill the formula down for each product.
- 1. Open your warranty workbook: Launch WPS Office, choose Open, and select your Excel workbook, or create a new file in WPS Spreadsheet.
- 2. Enter expiration dates: Enter a valid warranty expiration date in D16 or your chosen date column. Select the date cells and use Home > Number Format to apply a date format.
- 3. Add the status formula: Click a separate status cell, such as E16, type =IF(TODAY()>=D16,"Expired","Active"), and press Enter. Adjust D16 to match your date cell.
- 4. Fill down and save: Drag the formula cell's fill handle down to calculate other products' statuses. Use Save As and choose .xlsx if you need to share the workbook with Microsoft Excel users.
Frequently Asked Questions
Is the warranty marked Expired on the expiration date itself?
Yes. =IF(TODAY()>=D16,"Expired","Active") returns Expired when today equals D16. To keep coverage active through the listed date, change >= to >.
Will TODAY update the warranty status automatically at midnight?
Not necessarily. TODAY() updates when the workbook recalculates, not through a guaranteed midnight timer. In Excel desktop, select Formulas > Calculation Options > Automatic; opening the workbook or pressing F9 triggers recalculation.
Why does an empty expiration-date cell show Expired?
In the basic comparison, Excel treats a blank date cell like zero, so today's date is greater than it. Enter =IF(D16="","",IF(TODAY()>=D16,"Expired","Active")) in the status cell to leave missing dates blank.
Why is the formula returning the wrong warranty status?
Check that the formula references the correct expiration-date cell and that your device date is correct. Enter =ISNUMBER(D16) in an unused cell to test the date value. If it returns FALSE for a displayed date, re-enter that date as a recognized date value rather than merely changing its display format.
Can I use this warranty formula in WPS Spreadsheet?
Yes. Open the workbook in WPS Spreadsheet, select the status cell, and enter =IF(TODAY()>=D16,"Expired","Active"). Replace D16 with your expiration-date cell, then drag the fill handle down to apply it to other rows.
