Formatting Issues

Excel Conditional Formatting for Expiry Dates in Green, Amber, and Red

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

Create color-coded conditional-formatting rules for expiry dates while excluding cells marked Not required.

Excel Conditional Formatting for Expiry Dates in Green, Amber, and Red
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet tracks expiry dates and needs a one-month warning period.
Observed behavior
Dates more than one month away should be green, dates within one month should be amber, and expired or blank cells should be red. Not required should remain unformatted.
Before you start

Ensure expiry dates are stored as actual Excel dates, not text, and identify the first cell and range to format. The examples below assume date-only values in A2:A100 and use a calendar month as the warning period.

Solution 1Recommended

Create three formula-based expiry date rules in Excel

Use these rules to update the colors automatically as today's date changes.

A date equal to today is amber; it turns red the following day. Not required and other nonblank text are excluded because the date rules check for numeric values.

1
Select the date range

Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

2
Add the red rule

Enter =OR($A2="",AND(ISNUMBER($A2),$A2<TODAY())), select Format, choose a red fill, and click OK.

3
Add the amber rule

Choose Home > Conditional Formatting > New Rule and select the formula option again. Enter =AND(ISNUMBER($A2),$A2>=TODAY(),$A2<=EDATE(TODAY(),1)), choose an amber fill, and click OK.

4
Add the green rule

Create another formula rule with =AND(ISNUMBER($A2),$A2>EDATE(TODAY(),1)), choose a green fill, and click OK.

5
Check the rule range

Open Home > Conditional Formatting > Manage Rules and confirm that each rule applies to =$A$2:$A$100. If your dates are elsewhere, adjust the column and first-row references to match your selected range.

Create three formula-based expiry date rules in Excel
Dates stored as text: Text that looks like a date will not receive a date color. Convert it to an actual Excel date before applying the rules.
Format expiry dates in WPS

Track expiry dates with conditional formatting in WPS Spreadsheets

Use formula-based conditional formatting in WPS Spreadsheets to highlight expired, approaching, and valid dates while leaving Not required unformatted.

  1. 1. Select the dates: Open the worksheet in WPS Spreadsheets and select A2:A100, or select your own date range and adjust the formulas accordingly.
  2. 2. Create the red rule: Choose Home > Conditional Formatting > New Rule, select the formula-based rule option, and enter =OR($A2="",AND(ISNUMBER($A2),$A2<TODAY())). Set the fill to red and save the rule.
  3. 3. Create the amber rule: Add another formula-based rule with =AND(ISNUMBER($A2),$A2>=TODAY(),$A2<=EDATE(TODAY(),1)) and set its fill to amber.
  4. 4. Create the green rule: Add a third formula-based rule with =AND(ISNUMBER($A2),$A2>EDATE(TODAY(),1)) and set its fill to green.
Automatically highlight expiry dates with three status colorsLeave Not required and other nonblank text unformattedOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

What color is an expiry date that falls today?

It is amber under these rules. The red rule uses a date earlier than TODAY(), so the date becomes red the next day.

Why is a date not changing color?

The cell may contain a date stored as text. Convert it to a real Excel date, then check the range under Home > Conditional Formatting > Manage Rules.

How can I change the warning period to two months?

In both the amber and green formulas, replace EDATE(TODAY(),1) with EDATE(TODAY(),2). This keeps the two rules from overlapping.

Will cells marked Not required turn red?

No. The red rule targets blank cells and numeric dates earlier than today; the amber and green rules also require numeric dates.