Formatting Issues

How to Turn Expired Dates Red in Excel Using Conditional Formatting

Chanuka Geekiyanage Oct 19, 2026 868 views

Question details

The user needs to automatically highlight past or expired dates in red within a spreadsheet to easily track tasks and deadlines.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking deadlines, tasks, or expiration dates where past dates need to stand out visually as they become overdue.
Observed behavior: Dates earlier than the current date are automatically formatted with red text or a red background, and the rule dynamically updates each day.

Before you start

Ensure your date column contains valid Excel date values rather than plain text strings, so the software can correctly compare them against the current date.

Solution 1Recommended

Use Highlight Cells Rules to Turn Past Dates Red

The quickest way to format expired dates is by using the built-in Highlight Cells Rules combined with the TODAY function.

This method uses a quick preset in Excel to compare your selected dates against today's date, highlighting any dates that have already passed.

Select the date cells

Click and drag to highlight the range of cells containing the expiration dates you want to monitor.

Access Conditional Formatting

On the top ribbon, navigate to the Home tab, click on Conditional Formatting, select Highlight Cells Rules, and then choose Less Than.

Enter the TODAY formula

In the dialog box that appears, type =TODAY() in the first input field. This tells Excel to use the current day's date as the baseline.

Apply the red format

From the formatting dropdown menu on the right, select 'Light Red Fill with Dark Red Text' or 'Red Text', then click OK to apply.

Use Highlight Cells Rules to Turn Past Dates Red

Dynamic Updates: The =TODAY() function is dynamic, meaning your conditional formatting will automatically update every day without any manual changes.

Solution 2

Create a Custom Conditional Formatting Rule

Use this method if you need more custom formatting options, such as specific shades of red or custom borders, for your expired dates.

Efficient Spreadsheet Tool

Easily Highlight Expired Dates in WPS Office

WPS Spreadsheet provides powerful conditional formatting tools identical to Microsoft Excel. You can quickly track deadlines, manage your schedules, and apply dynamic date rules entirely for free.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates.
2. Select Dates: Highlight the column or cell range with your expiration dates.
3. Apply Rule: Click Home > Conditional Formatting > Highlight Cell Rules > Less Than.
4. Set Condition: Type =TODAY() in the box, pick a red formatting style from the dropdown, and click OK.

Free and lightweight spreadsheet softwareFully compatible with Microsoft Excel (.xlsx) formatsFamiliar interface with rich conditional formatting optionsBuilt-in functions like TODAY() work seamlessly

microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my expired dates turning red even after applying the rule?

This usually happens if your dates are formatted as text rather than valid Excel date values. Select your dates, go to the Data tab, and use the Text to Columns feature (clicking Finish immediately) to convert them into proper date formats.

Can I highlight dates that will expire in the next 7 days?

Yes. Create a new conditional formatting rule using a formula instead of a cell value. Use the formula =AND(A1>=TODAY(), A1<=TODAY()+7) (assuming A1 is your first cell) and apply your desired color.

Will the red formatting update automatically tomorrow?

Yes, because the =TODAY() function is dynamic. Every time you open the spreadsheet or it recalculates, it uses the current day's date to evaluate the formatting rule.

How do I remove the red formatting from my dates?

To clear the formatting, select the formatted cells, go to the Home tab, click Conditional Formatting, select Clear Rules, and choose 'Clear Rules from Selected Cells'.

Document & File Issues