Formatting Issues

How to Highlight Training Expiry Dates with Conditional Formatting in Excel

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to set up conditional formatting in a training matrix to automatically color-code employee certification statuses based on upcoming or past expiry dates.

Product: Excel
Device & OS: not provided
Scenario: Managing an employee training matrix tracking dozens of courses, requiring automatic visual cues to identify valid, expiring, and expired training records.
Observed behavior: The worksheet needs to display completed and valid training in green, training due for renewal within three months in amber, and expired training in red, without highlighting blank cells.

Before you start

Ensure the expiry dates in your training matrix are formatted as valid Excel date values, not plain text, so the dynamic date calculation formulas can function correctly.

Solution 1Recommended

Apply Formula-Based Conditional Formatting Rules

Set up customized, formula-driven conditional formatting rules using TODAY() and EDATE() to dynamically evaluate when training certificates expire.

By utilizing custom formulas for conditional formatting, you can create dynamic rules that update automatically based on the current date, effectively checking whether a date has passed or falls within a specific upcoming time frame.

Select the Target Range

Highlight the entire range of cells containing the expiry dates in your training matrix. Note the cell reference of the top-left cell in your selection (e.g., C2), as it will be used in your formulas.

Create the Red Rule for Expired Training

Navigate to Home > Conditional Formatting > New Rule. Select 'Use a formula to determine which cells to format'. Enter the formula =AND(C2<TODAY(), C2<>"") and click Format to set the Fill color to Red. Click OK.

Create the Amber Rule for Upcoming Expirations

Go to Home > Conditional Formatting > New Rule again. Use the formula =AND(C2>=TODAY(), C2<=EDATE(TODAY(),3), C2<>"") to identify dates expiring within three months. Set the format Fill color to Amber.

Create the Green Rule for Valid Training

Add a third rule via Conditional Formatting > New Rule. Enter the formula =AND(C2>EDATE(TODAY(),3), C2<>"") to mark training that is not due soon. Set the format Fill color to Green.

Review and Manage Rules

Go to Home > Conditional Formatting > Manage Rules to ensure all three rules apply to the correct range. The condition C2<>"" in each formula ensures that blank cells are ignored and not incorrectly colored.

Using Relative References: It is crucial to use relative cell references (like C2) instead of absolute references (like $C$2) in your conditional formatting formulas so the rule evaluates each row and column individually across the selected range.

Manage Training Matrices Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful, user-friendly conditional formatting features that perfectly mirror Excel's functionality. You can easily manage employee training matrices and set up dynamic date-based alerts to track expirations.

1. Open Your Training Matrix: Launch WPS Spreadsheet and open your existing training tracker.
2. Select the Dates: Highlight the range of cells containing the certification expiry dates.
3. Apply Conditional Formatting: Click Home > Conditional Formatting > New Rule from the top ribbon.
4. Input Custom Formulas: Select the formula option and enter your date-checking formulas, setting the corresponding red, amber, and green fill colors for each status.

Fully compatible with Microsoft Excel (.xlsx) files and advanced conditional formatting rules.Supports complex date functions including TODAY() and EDATE() for dynamic tracking.Free, lightweight, and features a familiar interface, making data management simple and fast.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my blank cells turning red when using conditional formatting for dates?

Spreadsheet software typically evaluates blank cells as the numeric value zero, which corresponds to a date in the past (January 0, 1900). Because this past date is less than TODAY(), the 'expired' rule triggers. To prevent this, always add a check to ensure the cell is not blank, such as =AND(C2<TODAY(), C2<>"").

How can I base the expiry color on a completion date instead of a pre-calculated expiry date?

If your matrix only lists the completion dates and the certification is valid for 2 years (24 months), you can adapt your formula. To check if it has expired, use =EDATE(C2, 24) < TODAY().

Why isn't my conditional formatting updating when a new day starts?

Formulas utilizing TODAY() update automatically when the workbook is opened or when the sheet recalculates. If the colors haven't updated, press F9 on your keyboard to force a manual recalculation, or check that your calculation options are set to 'Automatic' under the Formulas tab.

Document & File Issues